VBA to send mail with Outlook not working

Shadkng

Active Member
Joined
Oct 11, 2018
Messages
365
I am using the "SentOnBehalfOfName" in the below code, but the mail is getting sent from the default account and not the "sales@rfbcorp.com" that I specified. The weird thing is that it works correctly on one user in my company but not for 3 others with outlook. Is there another method to try? Any help is appreciated. Thanks

VBA Code:
Sub SEND_OUTLOOK_CONF_CLICK_FILENAME()
'Not using this macro.  Decided to use the "send outlook conf" which automatically attaches the file.

Dim oLook As Object
Dim oMail As Object
Dim FD As Object
Dim vrtSelectedItem As Variant
Dim strbody As String

    Set oLook = CreateObject("Outlook.Application")
    Set oMail = oLook.createitem(0)
    Set FD = Application.FileDialog(3)
    
    With oMail
        .SentOnBehalfOfName = "sales@rfbcorp.com"
        .To = Worksheets("DETAIL FORM").Range("F4")
        .cc = ""
        .HTMLBody = "See attached order confirmation. Your order will be released for production. Please notify us immediately if any changes are needed." & "<br/>" & "<br/>" _
                   & "Thank You" & "<br/>" & "<br/>" _
                 
        .Subject = "Friedland Confirmation" & " " & "PO " & Worksheets("DETAIL FORM").Range("B9").Value & ", S/M:" & " " & Worksheets("DETAIL FORM").Range("B8").Value
        
                FD.AllowMultiSelect = True
                FD.Filters.Clear
                FD.Filters.Add "All Files", "*.*"
                FD.InitialFileName = ActiveWorkbook.path & "\APDFQUOTES\"
                'filePath = ActiveWorkbook.path & "\APDFQUOTES\" & Worksheets("DETAIL FORM").Range("F15").Value & ".pdf"
                
                If FD.Show = True Then
       For Each vrtSelectedItem In FD.SelectedItems
       .Attachments.Add vrtSelectedItem
       
       Next
    End If
            
    .Display

    End With
    
    Set FD = Nothing
    Set oMail = Nothing
    Set oLook = Nothing
    
End Sub
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Using VBA to send Outlook Email / Sent on Behalf Of Name



As a follow-up, I did find the fix to this. I moved the ".SendOnBehalfName" down below the "To:" such that it looks like this:
Code:
With OutMail
.To = emailAdd
.SentOnBehalfOfName = emailAlias
.CC = ""
.BCC = ""
.Subject = "Corporate Transient Rate Audit " & auditDate & " / " & propName & " (#" & propID & ")"
.HTMLBody = "" & greeting & ""
.Send
End With
I've tested this and it works as it should.

Good luck to all!

Don
 
Upvote 0
I modified my code as below according to yours...not sure if got it right. I'm getting a compile error.

In the meantime, I changed the default "data" file to "sales@rfbcorp.com" which is the email I want sent from, and it seems to have fixed the problem. Not sure why it mattered.

VBA Code:
Sub SEND_OUTLOOK_DETAIL_FORM()

'this is used to open outlook and attach the detail form PDF which is generated macro DETAIL_FORM_SEND_OUTLOOK.  This macro is called in DETAIL_FORM_SEND_OUTLOOK.

Dim oLook As Object
Dim oMail As Object
Dim vrtSelectedItem As Variant
Dim strbody As String

    Set oLook = CreateObject("Outlook.Application")
    Set oMail = oLook.createitem(0)
       
    With OutMail
        .To = Worksheets("DETAIL FORM").Range("G3")
        .SentOnBehalfOfName = "sales@rfbcorp.com"
        .cc = ""
        .Subject = "Friedland Quotation" & " S/M:" & " " & Worksheets("DETAIL FORM").Range("B8").Value
        .HTMLBody = "See attached quotation. Please let us know if you have any questions." & "<br/>" & "<br/>" _
                   & "Thank You" & "<br/>" & "<br/>" _
              
        .Send
        End With
       
                .Attachments.Add ActiveWorkbook.path & "\APDFQUOTES\" & Worksheets("DETAIL FORM").Range("C2").Value & ".pdf"
                
    .Display

 
    
    Set oMail = Nothing
    Set oLook = Nothing
    
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top