Choose Outlook account to send email using VBA

Question

This is code to send emails from an Excel file.

I want to choose the Outlook account from which the emails are sent ("abc@abc.com").

Sub SendEmail()
Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Set Mail_Object = CreateObject("Outlook.Application")
For i = 2 To lr
    With Mail_Object.CreateItem(o)
        .Subject = Range("B" & i).Value
        .To = Range("A" & i).Value
        .Body = Range("C" & i).Value
        '.CC = Range("G" & i).Value
        '.Send
        .display 'disable display and enable send to send automatically
    End With
Next i
MsgBox "E-mail successfully sent", 64
Application.DisplayAlerts = False
Set Mail_Object = Nothing
End Sub

Does this answer your question? [How to send email from a specific Outlook account using Excel?](https://stackoverflow.com/questions/55360867/how-to-send-email-from-a-specific-outlook-account-using-excel) — niton, Jul 30 '22 at 03:00

score 1 · Accepted Answer · answered Jul 29 '22 at 10:56

1

I think you're after the SentOnBehalfOfName property:

With Mail_Object.CreateItem(o)
    .Subject = Range("B" & i).Value
    .To = Range("A" & i).Value
    .Body = Range("C" & i).Value
    .SentOnBehalfOfName = "abc@abc.com"
    '.CC = Range("G" & i).Value
    '.Send
    .display 'disable display and enable send to send automatically
End With

answered Jul 29 '22 at 10:56

CLR

11,284
1
11
29

It works perfectly! Thank you very much. It was much simpler than I thought, I was doing all crazy loops. – Lucas ORyan Aug 01 '22 at 08:35

Choose Outlook account to send email using VBA

1 Answers1