choose account in outlook when send mail via excel vba

Question

I want to send mails from a specific account in outlook from VBA in excel and Im stuck with my code, i went over and over the forums but it still doesnt work

I show you my code if anyone could help me it would be very very nice

Sub SendMail()

Dim objOutlook As Object
Dim objMail As Object
Dim ws As Worksheet

Set objOutlook = CreateObject("Outlook.Application")
Set ws = ActiveSheet
Dim signature As String
Dim LstRow As Long
LstRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

Dim oAccount As Outlook.Account

For Each oAccount In Outlook.Application.Session.Accounts

If oAccount = "mymail@server.com" Then

For Each cell In ws.Range("A4:A" & LstRow)

Set objMail = objOutlook.CreateItem(0)
signature = objMail.Body
    With objMail
        .To = cell.Value
        .Subject = cell.Offset(0, 1).Value
        .Body = cell.Offset(0, 2).Value & vbNewLine & signature
        .Attachments.Add cell.Offset(0, 3).Value
        .DeferredDeliveryTime = "15/03/2018 10:00:00 PM"
        .SendUsingAccount = oAccount
        .send
    End With

    Set objMail = Nothing
Next cell
Else
End If

Next
Set ws = Nothing
Set objOutlook = Nothing

End Sub

On which line you are getting error? did you try `.SentOnBehalfOfName` ? — Maddy, Mar 15 '18 at 09:23
this line is weird also because it go through even if the account does not exist 'If oAccount = "mymail@server.com" Then' — salom, Mar 15 '18 at 09:48
comment this line `.DeferredDeliveryTime = "15/03/2018 10:00:00 PM"` and then run. — Maddy, Mar 15 '18 at 09:55
I have two account in outlook, the default one and "mymail@server.com" and i want the mail to be send from "mymail@server.com" — salom, Mar 15 '18 at 11:15

score 6 · Accepted Answer · answered Mar 15 '18 at 11:34

The solution is just to put Set in front of .SendUsingAccount

 Set objMail = objOutlook.CreateItem(0)
signature = objMail.Body
   With objMail
    .To = cell.Value
    .Subject = cell.Offset(0, 1).Value
    .Body = cell.Offset(0, 2).Value & vbNewLine & signature
    .Attachments.Add cell.Offset(0, 3).Value
    .DeferredDeliveryTime = "15/03/2018 10:00:00 PM" 'need to comment here to run better
   Set .SendUsingAccount = oAccount
    .send
End With

And also Thanks to Maddy i commented after the deferredDeliveryTime and it went well through the oAccount

`Set .SendUsingAccount = oAccount` did not work, but `SentOnBehalfOfName` worked for me for `O365PP` for sending from another mailbox not my default one. — Timo, Oct 21 '21 at 15:16

choose account in outlook when send mail via excel vba

1 Answers1

Linked