Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 39

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

  1. #11
    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)

    I tested the code.
    I got an error.

    The pop-up say
    Run-time error '52'
    Bad File name or number

    -> Debug

    wFile = Dir(wPath & wPattern)

    Quote Originally Posted by DanteAmor View Post
    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

  2. #12
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,303
    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 tested the code.
    I got an error.

    The pop-up say
    Run-time error '52'
    Bad File name or number

    -> Debug

    wFile = Dir(wPath & wPattern)
    But what did you put in the cell in column I. You can show me.


    Obviously the file that you put in column I must exist in the folder.
    Regards Dante Amor

  3. #13
    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)

    C:\Users\ABC\Desktop\SavedFolder\Email1\test.JPG

    The file it already saved @ folder
    I also used the one file which u shared from dropbox to cfm the path is correct



    Quote Originally Posted by DanteAmor View Post
    But what did you put in the cell in column I. You can show me.


    Obviously the file that you put in column I must exist in the folder.
    Last edited by harky; Jul 16th, 2019 at 09:40 PM.

  4. #14
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,303
    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
    C:\Users\ABC\Desktop\SavedFolder\Email1\IW.xlsx

    The file it already saved @ folder
    I also used the one file which u shared from dropbox to cfm the path is correct
    In column H you should put:
    C:\Users\ABC\Desktop\SavedFolder\Email1\
    In column I you should put:
    IW.xlsx
    Regards Dante Amor

  5. #15
    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)

    oh........ i tot is direct link.
    There no way to use direct link?

    Dont like the idea of spilting folder path than filename.*

    Quote Originally Posted by DanteAmor View Post
    In column H you should put:


    In column I you should put:
    Last edited by harky; Jul 16th, 2019 at 09:49 PM.

  6. #16
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,303
    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 tested the code.
    I got an error.

    The pop-up say
    Run-time error '52'
    Bad File name or number

    -> Debug

    wFile = Dir(wPath & wPattern)
    They are different macros. Which of them are you testing?
    Regards Dante Amor

  7. #17
    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)

    I using this one which given by you. Not the dropbox one.

    My idea was
    Use either Folder or Direct Full link under Col H.


    But tht code given by ur is

    H = Folder Path
    I = Filename.**

    But since is SINGLE Direct Full link, i tot is better to use Col H rather than add more 1 col just for a filename

    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




    Quote Originally Posted by DanteAmor View Post
    They are different macros. Which of them are you testing?
    Last edited by harky; Jul 16th, 2019 at 11:27 PM.

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

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

    please, make a test. put the folder in H and in I the pattern or the file but in H only the folder.
    Regards Dante Amor

  9. #19
    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, i tested. Tht will works.

    Thanks.

    Quote Originally Posted by DanteAmor View Post
    please, make a test. put the folder in H and in I the pattern or the file but in H only the folder.

  10. #20
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,303
    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
    Dont like the idea of spilting folder path than filename.*

    Try this

    In H put the folder and the pattern
    Examples:

    C:\Users\ABC\Desktop\SavedFolder\Email1\test.JPG
    C:\Users\ABC\Desktop\SavedFolder\Email1\*.pdf
    C:\Users\ABC\Desktop\SavedFolder\Email1\*



    Code:
    Sub SendEmail()
        Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long, d As Long
        Dim wks As Worksheet, pf As String, 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
                 
                pf = wks.Range("H" & i).Value
                d = InStrRev(pf, "\")
                wPath = Left(pf, d)
                wPattern = Mid(pf, d + 1)
                If wPattern = "" Then wPattern = "*.*"
                'If Right(wPath, 1) <> "\" Then wPath = wPath & "\"
                If Dir(wPath, vbDirectory) <> "" Then
                    wFile = Dir(wPath & wPattern)
                    On Error Resume Next
                    Do While wFile <> ""
                        'If Dir(wPath & wFile) <> "" Then
                            .Attachments.Add wPath & wFile
                        'End If
                        wFile = Dir()
                    Loop
                    On Error GoTo 0
                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
    Now, you already have 2 versions, in a column and in 2 columns.
    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
  •