+ Reply to Thread
Results 1 to 4 of 4

Macro sending email from different email address.

Hybrid View

  1. #1
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Macro sending email from different email address.

    Hello!

    I have a macro that sends out an email from Outlook. I have two email addresses set up on my desktop Outlook account. Is it possible to have the macro specify which email address to send from? At the moment, I always have to change it in the email itself (from s.feintuch@hrea.org to training@hrea.org) and am wondering if it would be possible to not have to do this. Thanks!

     Sub SaveAsPDF()
        Dim sPath As String
        Dim sInv As String
        Dim rF As Range
        Dim sFile As String
        Dim sPCell As String
        Dim sResp As String
    
            sInv = ActiveSheet.Range("D17").Value
            Set rF = Worksheets("InvoicesDue").Range("B:B").Find(What:=sInv, LookAt:=xlWhole, LookIn:=xlValues)
            sPath = "\\hrea.sharepoint.com@SSL\DavWWWRoot\FinanceDocs\ELearning\Invoices"
            sFile = Replace(rF.Offset(0, -1) & "_" & rF.Offset(0, 13) & "_Invoice_" & Format(StrConv(rF.Offset(0, 3), vbProperCase), "DD-MMMM-YYYY") & "_" & rF.Offset(0, 1) & ".pdf", " ", "_")
            Application.ScreenUpdating = False
            ActiveSheet.Copy
            ActiveSheet.Buttons.Delete
            sPCell = ActiveSheet.UsedRange.Cells(1).Address
            ActiveSheet.UsedRange.Copy
            ActiveSheet.Range(sPCell).PasteSpecial xlPasteValues
            ActiveSheet.Range(sPCell).Select
            Application.CutCopyMode = False
            ActiveSheet.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=sPath & "/" & sFile, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
            ActiveWorkbook.Close False
            Application.ScreenUpdating = True
            sResp = MsgBox("File saved as an PDF file at:" & vbCr & sPath & vbCr & vbCr & _
                    "Do you want to create an email with the PDF attached to send?", vbYesNo + vbInformation)
            If sResp = vbYes Then
                Sheets("Email").Range("B1").Value = sInv
                Dim OutApp As Object
                Dim OutMail As Object
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)
                On Error Resume Next
                With OutMail
                    .To = Sheets("Email").Range("D2").Value
                    .CC = ""
                    .BCC = ""
                    .Subject = Sheets("Email").Range("D5").Value
                    .Body = Sheets("Email").Range("D7").Value
                    .Attachments.Add sPath & "/" & sFile
                    .Display
                End With
                On Error GoTo 0
            
                Set OutMail = Nothing
                Set OutApp = Nothing
            End If
        End Sub

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Macro sending email from different email address.

    There is a SenderEMailAddress property of the MailItem object
    Martin

  3. #3
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Macro sending email from different email address.

    Dear Martin,

    Sorry, I should have specified that I´m new to all of this! Is there a line I add in to the code to specify the SenderEmailAddress? I tried putting .From=training@hrea.org, but I'm clearly doing something wrong.

    Thanks!

  4. #4
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Macro sending email from different email address.

    Try

    With OutMail
                    .To = Sheets("Email").Range("D2").Value
                    .SenderEmailAddress= "training@hrea.org"
                    .CC = ""
                    .BCC = ""
                    .Subject = Sheets("Email").Range("D5").Value
                    .Body = Sheets("Email").Range("D7").Value
                    .Attachments.Add sPath & "/" & sFile
                    .Display
                End With

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. automation/macro for sending email to multiple email address
    By saurabhlotankar in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-30-2013, 12:13 PM
  2. Macro to PDF a sheet in workbook and email (outlook) to an email address in a cell
    By paul_sykes00 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-17-2012, 12:54 AM
  3. Sending Email with email address in cell 22
    By albert28 in forum Excel General
    Replies: 1
    Last Post: 01-19-2012, 11:24 AM
  4. Replies: 6
    Last Post: 12-02-2011, 02:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1