Microsoft Office Tutorials and References
In Depth Information
E‑mailing a Single Worksheet
Press Alt+F11 to go to the Visual Basic Editor.
From the menu at the top of the VBE, click Insert Module.
In the module you just created, type Sub EmailAttachmentRecipients and press Enter. VBA
will automatically place a pair of empty parentheses at the end of the Sub line, followed by
an empty line, and the End Sub line below that. Your macro will look like this so far:
Sub EmailAttachmentRecipients ()
End Sub
Declare and establish the Object variables for Outlook:
Dim objOutlook As Object
Dim objNameSpace As Object
Dim objInbox As Object
Dim objMailItem As Object
Set objOutlook = CreateObject(“Outlook.Application”)
Set objNameSpace = objOutlook.GetNamespace(“MAPI”)
Set objInbox = objNameSpace.Folders(1)
Set objMailItem = objOutlook.CreateItem(0)
Declare a String variable for the recipient list, and a Long variable for the count of cells in
column A that contain e-mail addresses:
Dim strTo As String
Dim i As Integer
strTo = “”
i = 1
Loop through the recipient e-mail addresses you entered from Step 1, in order to build a
continuous string where each recipient address is separated by a semicolon and a space, just as it
would appear in an Outlook “To” field:
strTo = strTo & Cells(i, 1).Value & “; “
i = i + 1
Loop Until IsEmpty(Cells(i, 1))
‘Remove the last two characters from the string,
‘which are an unneeded semicolon and space.
strTo = Mid(strTo, 1, Len(strTo) - 2)
Display the e-mail message:
With objMailItem
.To = strTo
.Subject = “Test of multiple recipients”
.Body = “Hello everyone, this is a test of multiple recipients with a workbook
.Attachments.Add ActiveWorkbook.FullName
.Display ‘Change to Send
End With
Search JabSto ::

Custom Search