Microsoft Office Tutorials and References
In Depth Information
E‑mailing a Single Worksheet
The active workbook you are attaching must be an actual workbook that has
been named and saved, or the code line .Attachments.Add ActiveWorkbook
.FullName will fail.
Release object variables from system memory:
Set objOutlook = Nothing
Set objNameSpace = Nothing
Set objInbox = Nothing
Set objMailItem = Nothing
When your macro is complete, it should look like this:
‘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 email addresses.
Dim strTo As String
Dim i As Integer
strTo = “”
i = 1
‘Loop through the recipient email 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 email message, including the attachment of the active workbook.
.To = strTo
.Subject = “Test of multiple recipients”
.Body = “Hello everyone, this is a test of multiple recipients with a workbook