choose account in outlook when send mail via excel vba

salom picture salom · Mar 15, 2018 · Viewed 21k times · Source

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

Answer

salom picture salom · Mar 15, 2018

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