How to change where an email is sent FROM in Excel VBA

0 votes

I discovered this VBA code for Excel that automatically sends emails using the addresses in Column A, any CC addresses in Column B, the email body in Column C, and the filepath of any attachments in Column D.

While I don't require Column B to contain CC, I do require that the email be sent from a particular address that is shared with coworkers. (instead of using my default personal, like it does now)

Here is how the Excel SS looks so far with currently 2 examples

Below is the VBA code I currently have and it works perfectly so far:

Sub Send_Files()


Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range
Dim FileCell As Range
Dim rng As Range

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set sh = Sheets("Sheet1")

Set OutApp = CreateObject("Outlook.Application")

For Each cell In sh.Columns("A").Cells.SpecialCells(xlCellTypeConstants)

    'Enter the path/file names in the D: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 = sh.Cells(cell.Row, 1).Value
            .cc = sh.Cells(cell.Row, 2).Value
            .Subject = "Example Subject 1"
            .Body = sh.Cells(cell.Row, 3).Value
            
            For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                If Trim(FileCell.Value) <> "" Then
                    If Dir(FileCell.Value) <> "" Then
                        .Attachments.Add FileCell.Value
                    End If
                End If
            Next FileCell
            
            .Send 'Or use .Display/Send
        End With
        
        Set OutMail = Nothing
    End If
Next cell

Set OutApp = Nothing

With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

End Sub
Mar 30, 2023 in Others by Kithuzzz
• 38,000 points
1,549 views

1 answer to this question.

0 votes

Inside the 'with out mail, insert this line:

      .SentOnBehalfOfName = "email_address@domain.com"

After making the necessary adjustments, it should appear as follows; simply swap out "email address@domain.com" for the desired email address.

      With OutMail
         .SentOnBehalfOfName = "email_address@domain.com"
         .to = sh.Cells(cell.Row, 1).Value
         .cc = sh.Cells(cell.Row, 2).Value
         .Subject = "Example Subject 1"
         .Body = sh.Cells(cell.Row, 3).Value     
answered Mar 30, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,600 points
3,783 views
0 votes
1 answer

How to programmatically code an 'undo' function in Excel-Vba?

Add the command button to the worksheet ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,600 points
1,046 views
0 votes
1 answer

How to separate Unit/Suite/APT/# from an address in Excel

Use batch geocode your file on geocoder.ca This ...READ MORE

answered Dec 10, 2022 in Others by narikkadan
• 63,600 points
737 views
0 votes
1 answer

Is it possible to get data from a webpage in real-time to an excel file?

The conventional method of obtaining data from ...READ MORE

answered Jan 17, 2023 in Others by narikkadan
• 63,600 points
535 views
0 votes
1 answer

VBA How to extract the date and time of arrival of a answered email

Use MailItem.ReceivedTime property. I hope this helps you ...READ MORE

answered Jan 9, 2023 in Others by narikkadan
• 63,600 points
2,904 views
0 votes
1 answer

Filter outlook sent items in vba failing for emails with multiple recipients

The PR DISPLAY TO parameter provides a ...READ MORE

answered Feb 21, 2023 in Others by narikkadan
• 63,600 points
778 views
0 votes
1 answer

Export Attachment from outlook to excel cells

You will need to save the attachment ...READ MORE

answered Apr 3, 2023 in Others by narikkadan
• 63,600 points
565 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,225 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,600 points
3,746 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,600 points
1,381 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP