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 = "[email protected]" 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

Solution

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

Comments

[Maddy]

On which line you are getting error? did you try .SentOnBehalfOfName ?

[salom]

There is no errors, juste the nail is not sent

[salom]

Yes I also tried senton behalf but it is the same thing

[Maddy]

Where is .Send Or .Display?

[salom]

sorry, it just after send using account

[salom]

this line is weird also because it go through even if the account does not exist 'If oAccount = "[email protected]" Then'

[Maddy]

comment this line .DeferredDeliveryTime = "15/03/2018 10:00:00 PM" and then run.

[salom]

Now it sending but not with the good account..

[Maddy]

good account means?

[salom]

I have two account in outlook, the default one and "[email protected]" and i want the mail to be send from "[email protected]"

[Maddy]

now try .SentOnBehalfOfName

[salom]

Still sending with the default account..

[salom]

I FIND THE SOLUTION :)

[salom]

I need to add set in front of .sendUsingAccount

[Maddy]

are you using Outlook 2016?

[salom]

yes I use outlook 2016

[Maddy]

:) great !!!!!!

[Timo]

Set .SendUsingAccount = oAccount did not work, but SentOnBehalfOfName worked for me for O365PP for sending from another mailbox not my default one.