.Display and .Send properties not working

jmazorra

Well-known Member
Joined
Mar 19, 2011
Messages
715
Hello everyone:

I can't figure out why my code when I use
VBA Code:
.Send

it gives Runtime error Automation error Unspecified error.

What am in forgetting?

VBA Code:
Option Explicit

Sub SendEmailAttachment()


Dim outlookApp As Object
Dim outMail As Object
Dim myAttachments As Object
Dim emailAddress As String
Dim emailAddressCC As String
Dim emailSubject As String
Dim fileName As String
Dim filePath As String
Dim attachment As String
Dim attachment2 As String
Dim signature As String
Dim lastrow As Integer
Dim x As Integer

 x = 2
 
 

Do While Sheet1.Cells(x, 1) <> ""
   
    Set outlookApp = CreateObject("Outlook.Application")
    Set outMail = outlookApp.CreateItem(0)
    Set myAttachments = outMail.Attachments
   
    
    filePath = ThisWorkbook.Path & "\"
    
    emailAddress = Sheet1.Cells(x, 3)
    emailAddressCC = Sheet1.Cells(x, 13)
    emailSubject = Sheet1.Cells(x, 17)
    fileName = Sheet1.Cells(x, 16)
    
    attachment = filePath + fileName
    attachment2 = filePath + "CEO Letter to Employees.Pdf"
    
    With outMail
    .Send

End With

signature = outMail.HTMLbody


With outMail

        .SentOnBehalfOfName = "sales@domain.com"
        .To = emailAddress
        .cc = emailAddressCC
        .bcc = ""
        .Subject = emailSubject
        .HTMLbody = "Please find your statement attached" & vbCrLf & "Best Regards" & signature
          
            
        myAttachments.Add (attachment2)
        myAttachments.Add (attachment)
        .Send
   
            
        lastrow = lastrow + 1
        emailAddress = ""
        
    x = x + 1

Set outlookApp = Nothing
Set outMail = Nothing

End With

Loop

End Sub
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
I am not 100% sure this is your problem but you are calling .Send before you have assigned any of the properties of outMail. There is nothing to send yet. You should move the Send down to just before you set outMail to Nothing.

Also you should create outlookApp outside of the loop instead of doing it for every email. You just need one instance for all the emails.
 
Upvote 0
I am not 100% sure this is your problem but you are calling .Send before you have assigned any of the properties of outMail. There is nothing to send yet. You should move the Send down to just before you set outMail to Nothing.

Also you should create outlookApp outside of the loop instead of doing it for every email. You just need one instance for all the emails.
Tried that and still got the Automation Error
 
Upvote 0
Again, not sure if this is causing your problem, but on a second look you have two calls to .Send. Remove one of them.
 
Upvote 0
Again, not sure if this is causing your problem, but on a second look you have two calls to .Send. Remove one of them.
Figured out that the issue was that I had not loaded the correct library and needed to update to the newer .Net.
 
Upvote 0
I don't know why you would need a .NET library for this but OK.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top