Can't add signature to mass email VBA nor change the from account

Question

I'm a super beginner and I've managed to put the code below from watching numerous youtube videos I can't manage to get the signature onto the email and change the from account With this code the signature is inserted but it doesn't keep the same formatting I tried another one that copied it from a word document but when I tried pasting it it never worked Could anyone help me? I have tried all the posts I've found on StackOverflow and google searches

Sub Send_Mails()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim I As Integer

Dim OA As Object
Dim msg As Object
Dim OutAccount As Outlook.Account

Set OA = CreateObject("outlook.application")

Dim last_row As Integer
last_row = Application.CountA(sh.Range("A:A"))

Set msg = OA.CreateItem(0)

With msg
        .Display
    End With
    Signature = msg.Body

For I = 2 To last_row

'msg.Display

msg.To = sh.Range("A" & I).Value
msg.cc = sh.Range("B" & I).Value
msg.Subject = sh.Range("C" & I).Value
msg.Body = sh.Range("D" & I).Value & Signature

    
     
If sh.Range("E" & I).Value <> "" Then
msg.attachments.Add sh.Range("E" & I).Value
End If
 

'msg.send


sh.Range("F" & I).Value = "Sent"

Next I

MsgBox "All the mails have been sent successfully"

End Sub

So you are trying to send out mass emails and have it look like it's coming from someone besides you? Why would you need to do that? — braX, Dec 04 '21 at 22:28
Because we have a general account ex. Info@company.com and we send out all the official communications from that account :) — MWanderlust, Dec 05 '21 at 01:00
Take a look at [Send email from another address using VBA](https://www.slipstick.com/developer/code-samples/send-email-address-vba) and [Insert Outlook Signature in mail](https://www.rondebruin.nl/win/s1/outlook/signature.htm). — Elio Fernandes, Dec 05 '21 at 11:58
Use `.HTMLBody` https://stackoverflow.com/a/12990825/1571407. — niton, Dec 05 '21 at 20:27
@ElioFernandes thank you so much! the first part worked perfectly sendonbehalfof :) — MWanderlust, Dec 06 '21 at 11:48
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. — Community, Dec 10 '21 at 05:07
@community should I edit it even though I got the answers to both questions ? I was able to change the from account using the send on behalf of and add the signature using the HTML.body property — MWanderlust, Dec 11 '21 at 07:01

score 0 · Answer 1 · answered Dec 06 '21 at 11:48

The signature doesn't preserve the original formatting because a plain-text property was used in the code.

Signature = msg.Body

Instead, you need to use the HTMLBody property which returns an HTML markup with all formatting set up. Also you may consider using the Word object model (see the WordEditor property of the Inspector class).

To set up the From account correctly to the one which is set up in an Outlook profile you can use the SendUsingAccount property which returns or sets an Account object that represents the account under which the MailItem is to be sent. For example:

Sub SendUsingAccount() 
 Dim oAccount As Outlook.account
 For Each oAccount In Application.Session.Accounts
   If oAccount.AccountType = olPop3 Then 
     Dim oMail As Outlook.MailItem 
     Set oMail = Application.CreateItem(olMailItem) 
     oMail.Subject = "Sent using POP3 Account" 
     oMail.Recipients.Add ("someone@example.com") 
     oMail.Recipients.ResolveAll 
     Set oMail.SendUsingAccount = oAccount 
     oMail.Send 
   End If 
 Next 
End Sub

Also if you have configured sufficient privileges on the Exchange server side you may consider using the SentOnBehalfOfName property which returns a string indicating the display name for the intended sender of the mail message.

Can't add signature to mass email VBA nor change the from account

1 Answers1