View Single Post
  #1 (permalink)  
Old October 5th, 2014, 05:39 AM
kennfong83 kennfong83 is offline
Registered User
Points: 5, Level: 1
Points: 5, Level: 1 Points: 5, Level: 1 Points: 5, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2014
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Chapter 28 E-Mailing A Single Worksheet

I follow Tom's book exercise to send worksheet to 4 recipients but it end up with run time error 2147467259 - operation failed.
Can i know what is this problem?

The code as below:

Sub EmailAttachmentRecipients()

Dim objOutlook As Object
Dim objNameSpace As Object
Dim objInbox As Object
Dim objMailItem
Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNameSpace("MAPI")
Set objInbox = objNameSpace.Folders(1)
Set objMailItem = objOutlook.CreateItem(0)

Dim strTo As String
Dim i As Integer
strTo = " "
i = 1

Do
strTo = strTo & Cells(i, 1).Value & ";"
i = i + 1
Loop Until IsEmpty(Cells(i, 1))
strTo = Mid(strTo, 1, Len(strTo) - 2)

With objMailItem
.To = strTo
.Subject = "Test of multiple recipients"
.Body = "Hello everyone, this is a test of multiple recipients with a workbook attachment."
.Attachments.Add ActiveWorkbook.FullName
.Display 'Change to Send
End With

Set objOutlook = Nothing
Set objNameSpace = Nothing
Set objInbox = Nothing
Set objMailItem = Nothing

End Sub