Specify From in an Email

[cfoye130]

I have this code, that sends an email.....

Sub Emailer()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim sh As Worksheet
    Dim cell As Range, FileCell As Range, rng As Range
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    Set sh = Sheet5
    Set OutApp = CreateObject("Outlook.Application")
    For Each cell In sh.Columns("C").Cells.SpecialCells(xlCellTypeConstants)
        'Enter the file names in the C:Z column in each row
        Set rng = sh.Cells(cell.Row, 1).Range("D1:Z1")
        If cell.Value Like "?*@?*.?*" And _
           Application.WorksheetFunction.CountA(rng) > 0 Then
            Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .To = cell.Value
                .Subject = "UAT Daily Status - " & cell.Offset(0, -1).Value & " - " & Format(Date, ddmmyyyy)
                .Body = ""
                
                
                For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                    If Trim(FileCell) <> "" Then
                        If Dir(FileCell.Value) <> "" Then
                            .Attachments.Add FileCell.Value
                        End If
                    End If
                Next FileCell
                
                .Send  'Or use Display
            End With
            Set OutMail = Nothing
        End If
    Next cell
    Set OutApp = Nothing
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Is there a way to specify the from on the email. Currently it sends from the default account, but I would rather it send from a shared mailbox in outlook, "Aurora UAT Department".

Any ideas?

 

[jimrward]

try the following adapt as required

'****
' Create a new email
'****

Dim msg As Outlook.MailItem
Set msg = Application.CreateItem(olMailItem)
msg.to = "anyone@abc.com"
msg.SentOnBehalfOfName = "complaints"
msg.Subject = "Your Reference :- " & RefName

msg.Body = strMailBody
msg.send
msg.Display

choose msg.send or msg.display depending if you wish to add more or send it immediately

 

[cfoye130]

Works!

Thank you!