VBA to update "from" field when sending an email from Excel?

[franswa3434]

Good afternoon everyone,

I got some code that will automatically send an email based upon cell value.

I got the To and CC fields right, but I cannot get the From field to send from the right mailbox/distro.

Anyone know how I can do this?

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next
    If ActiveSheet.Range("J2") = "Waiting for Response From Carrier" Then
    Call Mail_small_Text_Outlook
    
    End If
    
End Sub
Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
        xMailBody = "Hello," & vbNewLine & vbNewLine & _
              "Would you please provide me a quote for the following service:" & vbNewLine & vbNewLine & _
              "Address: " & ActiveSheet.Range("F2") & ", " & ActiveSheet.Range("G2") & ", " & ActiveSheet.Range("H2") & " " & ActiveSheet.Range("I2") & vbNewLine & _
              "Service: " & ActiveSheet.Range("C2") & "Mb/" & ActiveSheet.Range("D2") & "Mb - " & ActiveSheet.Range("E2") & vbNewLine & vbNewLine & _
              "Thank you,"
  
    On Error Resume Next
    With xOutMail
        .To = ActiveSheet.Range("N2")
        .CC = ActiveSheet.Range("P2")
        .BCC = ""
        .Subject = "Service Request - " & ActiveSheet.Range("D4") & " " & ActiveSheet.Range("F2") & ", " & ActiveSheet.Range("G2") & ", " & ActiveSheet.Range("H2") & " " & ActiveSheet.Range("I2")
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

 

[iliace]

I have done this before, but cannot recall how. It was at an old job, where the e-mails could be sent from either your primary account, or a central department account. I think the trick lies in using the Recipient object in Outlook, which has a Type property and an olOriginator option (integer value 0) for that property - so perhaps look into that possibility.

Unfortunately, I no longer have an easy way to set up multiple accounts in Outlook, and don't exactly remember how to make it work.

Sorry I can't be of better assistance. Best of luck to you.

 

[daverunt]

Maybe this:

.SentOnBehalfOfName = "Emailadress"

 

[franswa3434]

This worked perfectly! Thank you!