none
Macro to send emails with attachment based on cell values and file names RRS feed

  • Question

  • Good Day!

    I have an excel file (master_file.xlsx) which contains the following column:

    

    Now I need to create a Macro that will automatically send excel files based on above master_file.xlsx column = Code. 

    Ex. 

    111data_sample.xlsx file must be sent to emails in cell B2

    123data_sample1.xlsx file must be sent to email in cell B3

    254date_sample2.xlsx file must be sent to email in cell B4

    I have hundreds of Rows of Code and Emails which I am sending manually and this takes too much time.

    Any help will be much appreciated.

    Thank You!

    Regem

    Monday, September 16, 2019 8:44 AM

Answers

  • The following code will work best if Outlook is already running:

    Sub SendFiles()
        Dim r As Long
        Dim m As Long
        Dim sPath As String
        Dim sMail As String
        Dim sFile As String
        Dim oApp As Object
        Dim oMsg As Object
        Dim a As Variant
        Dim v As Variant
        Set oApp = CreateObject(Class:="Outlook.Application")
        m = Range("A" & Rows.Count).End(xlUp).Row
        For r = 2 To m
            sMail = Range("B" & r).Value
            sFile = Range("A" & r).Value & "data_sample.xlsx"
            Set oMsg = oApp.CreateItem(0)
            With oMsg
                .Subject = "Change this to the subject"
                .Body = "Change this to the message body" & vbCrLf & _
                    "Yours sincerely," & vbCrLf & "Regem Mejogue"
                a = Split(sMail, ";")
                For Each v In a
                    .Recipients.Add Trim(v)
                Next v
                .Attachments.Add sFile
                ' Use either .Display or .Send, do not use both!
                ' For testing: display the message
                .Display
                ' For production: send the message
                .Send
            End With
        Next r
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Regem Mejogue Thursday, September 19, 2019 2:02 PM
    Wednesday, September 18, 2019 3:56 PM

All replies

  • How do you determine the file name for the attachment?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, September 16, 2019 9:41 AM
  • Hello Hans,

    The file name is determined as the first 3 character which represents the Code and corresponding email address.

    I don't know if this is possible but looking forward to any options that this can be automated.

    Thank you very much!

    Regem

    Wednesday, September 18, 2019 11:51 AM
  • I'm afraid I don't understand yet how we can construct a file name such as

    254date_sample2.xlsx

    I understand the "254" (from column A) and "date_sample" (a fixed part). But where does the 2 come from?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, September 18, 2019 12:49 PM
  • Hello Hans,

    The files are exported from a system and already available. These files are located in one folder in which I need to send automatically. 

    Please nevermind the "date_sample2" since each file have different file names. The only link to the email address in which the file will be sent is its file name's first 3 character Ex. 254 - "254date_sample2.xlsxfile send to sample_email4@yahoo.com

    Thank you!

    Regem


    • Edited by Regem Mejogue Wednesday, September 18, 2019 1:24 PM spelling
    Wednesday, September 18, 2019 1:19 PM
  • Hi Hans,

    I can make the file names constant except the first 3 character.

    Ex.  "data_sample" as fixed part.

    234data_sample.xlsx

    123data_sample.xlsx

    111data_sample.xlsx

    Thank you.

    Regem


    • Edited by Regem Mejogue Wednesday, September 18, 2019 1:39 PM fixed
    Wednesday, September 18, 2019 1:37 PM
  • The following code will work best if Outlook is already running:

    Sub SendFiles()
        Dim r As Long
        Dim m As Long
        Dim sPath As String
        Dim sMail As String
        Dim sFile As String
        Dim oApp As Object
        Dim oMsg As Object
        Dim a As Variant
        Dim v As Variant
        Set oApp = CreateObject(Class:="Outlook.Application")
        m = Range("A" & Rows.Count).End(xlUp).Row
        For r = 2 To m
            sMail = Range("B" & r).Value
            sFile = Range("A" & r).Value & "data_sample.xlsx"
            Set oMsg = oApp.CreateItem(0)
            With oMsg
                .Subject = "Change this to the subject"
                .Body = "Change this to the message body" & vbCrLf & _
                    "Yours sincerely," & vbCrLf & "Regem Mejogue"
                a = Split(sMail, ";")
                For Each v In a
                    .Recipients.Add Trim(v)
                Next v
                .Attachments.Add sFile
                ' Use either .Display or .Send, do not use both!
                ' For testing: display the message
                .Display
                ' For production: send the message
                .Send
            End With
        Next r
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Regem Mejogue Thursday, September 19, 2019 2:02 PM
    Wednesday, September 18, 2019 3:56 PM
  • Hello Hans,

    This is great!

    I set value in sPath and concatenate this with sFile .

    Thank you very much!

    Regards,

    Regem


    Thursday, September 19, 2019 2:11 PM