Saturday, 14 April 2018

How to send an email from another mailbox in Excel VBA | Use on behalf in VBA to send mail from another account | VBA email automation

If you wish to send a mail from an another email account, you can add the account name to SentOnBehalfOfName property of your mail object.

Note : Sometimes, the mailboxes takes time to respond, so I added a while loop to make sure that its' been added. You may add a counter or time variable to exit from the while loop if your mailbox is taking too long to respond, so you will not end up in a infinite loop.

Example :

Dim OutMail As Object, Cell As Range, OutApp As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = ""
        .CC = ""
        '.BCC = ""
        .Subject = "subject"
        'check errors & timing
        On Error Resume Next
        .SentOnBehalfOfName = ""
        Do While Err.Number <> 0
            .SentOnBehalfOfName = MailBody.Range("B3")
        On Error GoTo 0
        .HTMLBody =  "this is body"

    End With