Page 1 of 4 123 ... LastLast
Results 1 to 10 of 39

Thread: VBA to send emails attachment base on path (folder)

  1. #1
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to send emails attachment base on path (folder)

    A B C D E F
    S/N To: cc Subject Body Path of Attachment folder
    1 abc@email1.com abc@email1.com test email 1 Hello Email C:\Users\ABC\Desktop\SavedFolder\Folder1

    I had a code below, possible to attach a code which will attach any file tht found in Path of Attachment folder?
    If no folder or nth in the folder, it will be ignored.



    Code:
    Sub SendEmail()
    
    
    'START of confirmation message box'
        response = MsgBox("Start sending email?", vbYesNo)
        If response = vbNo Then
        MsgBox ("Macro Canceled!")
        Exit Sub
        End If
    'END of confirmation message box'
    
    
    Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
    Dim wks As Worksheet
    
    
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
    
    Set Mail_Object = CreateObject("Outlook.Application")
    Set wks = Worksheets("send_email")
    
    
    For i = 2 To lr
            With Mail_Object.CreateItem(o)
                .To = wks.Range("B" & i).Value
                .CC = wks.Range("C" & i).Value
                .Subject = wks.Range("D" & i).Value
                .Body = wks.Range("E" & i).Value
                .Send
                '.display 'disable display and enable send to send automatically
                Application.Wait (Now + TimeValue("0:00:03")) 'Pausing an application for 3s, before next email
        End With
    Next i
            MsgBox "E-mail successfully sent", 64
            Application.DisplayAlerts = False
    Set Mail_Object = Nothing
    End Sub
    Last edited by harky; Jul 15th, 2019 at 02:47 AM.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,261
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA to send emails attachment base on path (folder)

    Quote Originally Posted by harky View Post
    I had a code below, possible to attach a code which will attach any file tht found in Path of Attachment folder?
    If no folder or nth in the folder, it will be ignored.
    Try this

    Code:
    Sub SendEmail()
        Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
        Dim wks As Worksheet, wPath As String, wFile As Variant
    
    
        'START of confirmation message box'
        response = MsgBox("Start sending email?", vbYesNo)
        If response = vbNo Then
            MsgBox ("Macro Canceled!")
            Exit Sub
        End If
        'END of confirmation message box'
        lr = Cells(Rows.Count, "B").End(xlUp).Row
        Set Mail_Object = CreateObject("Outlook.Application")
        Set wks = Worksheets("send_email")
        For i = 2 To lr
            With Mail_Object.CreateItem(o)
                .To = wks.Range("B" & i).Value
                .CC = wks.Range("C" & i).Value
                .Subject = wks.Range("D" & i).Value
                .Body = wks.Range("E" & i).Value
                wPath = wks.Range("F" & i).Value
                If Right(wPath, 1) <> "\" Then wPath = wPath & "\"
                If Dir(wPath, vbDirectory) <> "" Then
                    wFile = Dir(wPath & "*.*")
                    Do While wFile <> ""
                        .Attachments.Add wPath & wFile
                        wFile = Dir()
                    Loop
                End If
                .Send
                '.display 'disable display and enable send to send automatically
                Application.Wait (Now + TimeValue("0:00:03")) 'Pausing an application for 3s, before next email
            End With
        Next i
        MsgBox "E-mail successfully sent", 64
        Application.DisplayAlerts = False
        Set Mail_Object = Nothing
    End Sub
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to send emails attachment base on path (folder)

    Thanks! this works great.. thanks pal!

    Quote Originally Posted by DanteAmor View Post
    Try this

    Code:
    Sub SendEmail()
        Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
        Dim wks As Worksheet, wPath As String, wFile As Variant
    
    
        'START of confirmation message box'
        response = MsgBox("Start sending email?", vbYesNo)
        If response = vbNo Then
            MsgBox ("Macro Canceled!")
            Exit Sub
        End If
        'END of confirmation message box'
        lr = Cells(Rows.Count, "B").End(xlUp).Row
        Set Mail_Object = CreateObject("Outlook.Application")
        Set wks = Worksheets("send_email")
        For i = 2 To lr
            With Mail_Object.CreateItem(o)
                .To = wks.Range("B" & i).Value
                .CC = wks.Range("C" & i).Value
                .Subject = wks.Range("D" & i).Value
                .Body = wks.Range("E" & i).Value
                wPath = wks.Range("F" & i).Value
                If Right(wPath, 1) <> "\" Then wPath = wPath & "\"
                If Dir(wPath, vbDirectory) <> "" Then
                    wFile = Dir(wPath & "*.*")
                    Do While wFile <> ""
                        .Attachments.Add wPath & wFile
                        wFile = Dir()
                    Loop
                End If
                .Send
                '.display 'disable display and enable send to send automatically
                Application.Wait (Now + TimeValue("0:00:03")) 'Pausing an application for 3s, before next email
            End With
        Next i
        MsgBox "E-mail successfully sent", 64
        Application.DisplayAlerts = False
        Set Mail_Object = Nothing
    End Sub

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,261
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA to send emails attachment base on path (folder)

    Quote Originally Posted by harky View Post
    Thanks! this works great.. thanks pal!
    Youre welcome, thanks for the feedback.
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to send emails attachment base on path (folder)

    Quote Originally Posted by DanteAmor View Post
    Youre welcome, thanks for the feedback.
    Hi, I think of extent the body into 3 part. How will it be done :D


    A B C D E F E F
    S/N To: cc Subject Greeting Body Text Signature Path of Attachment folder
    1 abc@email1.com abc@email1.com test email 1 Hi Tone For your information... Regards. C:\Users\ABC\Desktop\SavedFolder\Folder1


  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,261
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA to send emails attachment base on path (folder)

    Quote Originally Posted by harky View Post
    Hi, I think of extent the body into 3 part.
    Replace this lines.

    Code:
    .Body = wks.Range("E" & i).Value & " " & _
    wks.Range("F" & i).Value & " " & _
    wks.Range("G" & i).Value
                wPath = wks.Range("H" & i).Value
    Regards Dante Amor

  7. #7
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to send emails attachment base on path (folder)

    Thanks!

    I added a break

    Code:
               .Body = wks.Range("E" & i).Value & vbNewLine & _
                wks.Range("F" & i).Value & vbNewLine & _
                wks.Range("G" & i).Value
    Quote Originally Posted by DanteAmor View Post
    Replace this lines.

    Code:
    .Body = wks.Range("E" & i).Value & " " & _
    wks.Range("F" & i).Value & " " & _
    wks.Range("G" & i).Value
                wPath = wks.Range("H" & i).Value

  8. #8
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to send emails attachment base on path (folder)

    Hi Dante,

    Regarding on the Path of Attachment folder.

    Can help me?
    I was thinking if possible attach email by

    wherever all files* found in folder or by direct file path (if only 1 file)

    e.g
    C:\Users\ABC\Desktop\SavedFolder\Folder1\ (more than 1)
    C:\Users\ABC\Desktop\SavedFolder\Folder1\abc.jpg (can be jpg, pdf, zip etc) (if only 1 file)

    Code:
    Sub SendEmail2()
        Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
        Dim wks As Worksheet, wPath As String, wFile As Variant
    
    
        'START of confirmation message box'
        response = MsgBox("Start sending email?", vbYesNo)
        If response = vbNo Then
            MsgBox ("Macro Canceled!")
            Exit Sub
        End If
        'END of confirmation message box'
        
        lr = Cells(Rows.Count, "B").End(xlUp).Row
        Set Mail_Object = CreateObject("Outlook.Application")
        Set wks = Worksheets("SendEmail_MOD2")  'worksheet name
        For i = 2 To lr
            With Mail_Object.CreateItem(o)
                .To = wks.Range("B" & i).Value
                .CC = wks.Range("C" & i).Value
                '.BCC = wks.Range("G" & I).Value    'G is refer to column G in excel
                .Subject = wks.Range("D" & i).Value
                
                .Body = wks.Range("E" & i).Value & vbNewLine & _
                wks.Range("F" & i).Value & vbNewLine & _
                wks.Range("G" & i).Value
                
                wPath = wks.Range("H" & i).Value
                If Right(wPath, 1) <> "" Then wPath = wPath & ""
                If Dir(wPath, vbDirectory) <> "" Then
                    wFile = Dir(wPath & "*.*")
                    Do While wFile <> ""
                        .Attachments.Add wPath & wFile
                        wFile = Dir()
                    Loop
                End If
                
                'Send
                .display 'disable display and enable send to send automatically
                Application.Wait (Now + TimeValue("0:00:03")) 'Pausing an application for 3s, before next email
            End With
        Next i
        MsgBox "E-mail successfully sent", 64
        Application.DisplayAlerts = False
        Set Mail_Object = Nothing
    End Sub


    Quote Originally Posted by DanteAmor View Post
    Replace this lines.

    Code:
    .Body = wks.Range("E" & i).Value & " " & _
    wks.Range("F" & i).Value & " " & _
    wks.Range("G" & i).Value
                wPath = wks.Range("H" & i).Value
    Last edited by harky; Jul 16th, 2019 at 02:21 AM.

  9. #9
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to send emails attachment base on path (folder)



    A B C D E F E F
    S/N To: cc Subject Greeting Body Text Signature Path of Attachment folder
    1 abc@email1.com abc@email1.com test email 1 Hi Tone For your information... Regards. C:\Users\ABC\Desktop\SavedFolder\Folder1\
    *all attach all files found in folder
    2 abc@email2.com abc@email2.com test email 2 hi tester2 For your information.. Regards. C:\Users\ABC\Desktop\SavedFolder\Folder2\abc.pdf * or direct path - can be jpg, pdf, zip, doc*


    Last edited by harky; Jul 16th, 2019 at 04:09 AM.

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,261
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA to send emails attachment base on path (folder)

    Try this.


    In column H the folder. In column I the pattern. It can be:
    *.pdf
    *.xlsx
    or directly the file
    dat.jpeg

    Code:
    Sub SendEmail()
        Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
        Dim wks As Worksheet, wPath As String, wFile As Variant, wPattern As String
    
    
        'START of confirmation message box'
        response = MsgBox("Start sending email?", vbYesNo)
        If response = vbNo Then
            MsgBox ("Macro Canceled!")
            Exit Sub
        End If
        'END of confirmation message box'
        Set Mail_Object = CreateObject("Outlook.Application")
        Set wks = Worksheets("send_email")
        lr = wks.Cells(Rows.Count, "B").End(xlUp).Row
        For i = 2 To lr
            With Mail_Object.CreateItem(o)
                .to = wks.Range("B" & i).Value
                .cc = wks.Range("C" & i).Value
                .Subject = wks.Range("D" & i).Value
                .Body = wks.Range("E" & i).Value & vbNewLine & _
                    wks.Range("F" & i).Value & vbNewLine & _
                    wks.Range("G" & i).Value
                    
                wPath = wks.Range("H" & i).Value
                wPattern = wks.Range("I" & i).Value
                If wPattern = "" Then wPattern = "*.*"
                If Right(wPath, 1) <> "\" Then wPath = wPath & "\"
                If Dir(wPath, vbDirectory) <> "" Then
                    wFile = Dir(wPath & wPattern)
                    Do While wFile <> ""
                        .Attachments.Add wPath & wFile
                        wFile = Dir()
                    Loop
                End If
                .Send
                '.display 'disable display and enable send to send automatically
                Application.Wait (Now + TimeValue("0:00:03")) 'Pausing an application for 3s, before next email
            End With
        Next i
        MsgBox "E-mail successfully sent", 64
        Application.DisplayAlerts = False
        Set Mail_Object = Nothing
    End Sub
    I have an app with something similar your need, you can download the file and maybe it will help you.

    https://www.dropbox.com/s/kb6xci9y4r...html.xlsm?dl=0
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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