p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: Excel VBA 24-Hour Trainer (http://p2p.wrox.com/forumdisplay.php?f=635)
-   -   Chapter 28 E-Mailing A Single Worksheet (http://p2p.wrox.com/showthread.php?t=93657)

kennfong83 October 5th, 2014 05:39 AM

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

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

Tom Urtis January 6th, 2016 12:35 AM

Hi, and thanks for buying the book.

That error can be due to a couple of reasons. For example, if the strTo variable contains an illogical recipient name (such as, in a cell, a name has a space or disallowed character), VBA would raise an error. It might also be due to a server issue or a default IT setting that does not allow, or expect that, Outlook will be required to send the email. Hard to say without knowing the background of the environment you're working with.

All times are GMT -4. The time now is 03:08 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.