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 ("[email protected]").

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

Answer 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 = "[email protected]"
    '.CC = Range("G" & i).Value
    '.Send
    .display 'disable display and enable send to send automatically
End With