CODEWITHSUNDEEP
htmlexcelvba
pchung24
pchung24

Reputation: 23

Sending out email with signature?

I am creating a VBA code in Excel for a bulk email with a signature.

My code works when I use .Display.

When I run my code with .Send I get an error

Run-time error '1525415670 The item has been moved or deleted

highlighting .HTMLBody = "Hello" & .HTMLBody.

I have moved the .HTMLBody = "Hello" & .HTMLBody above the .Send code, but then the signature does not show up in my email.

In addition is there a way to customize my body so that I can mass email with a different URL link to each recipient?

I would like to leave the line .Body = Sheet1.Cells (I, 3).Value to do this.

Is there a way I can keep both lines of code:

.Body = Sheet1.Cells(I, 3).Value

.HTMLBody = "Hello" & .HTMLBody

Sub Send_Multiple_Emails_Two_Sheet1()
    
    Dim ol As Outlook.Application
    Dim olmail As Outlook.MailItem
    
    Set ol = New Outlook.Application
    
    For i = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    
        Set olmail = ol.CreateItem(olMailItem)
        
        With olmail
            .To = Sheet1.Cells(i, 1).Value
            .Subject = Sheet1.Cells(i, 2).Value
            .Body = Sheet1.Cells(I,3).Value
            .Send
            .HTMLBody = "Hello" & .HTMLBody
        End With
    
    Next
    
End Sub

I would like the output to come out with:

Strbody (Body of email) & .HTMLBody (Signature)

Upvotes: 0

Views: 463

Answers (1)

Alp Aribal
Alp Aribal

Reputation: 370

Once you Send the email, you can not access the HTMLBody anymore as the item is already gone. Hence, Send has to be at the very end.

I think you need to Display first in order to have the signature. Try this:

With olmail
    .Display
    .To = Sheet1.Cells(i, 1).Value
    .Subject = Sheet1.Cells(i, 2).Value
    .HTMLBody = "Hello" & .HTMLBody
    .Send

Upvotes: 1

Related Questions