Page 4 of 4 FirstFirst ... 234
Results 31 to 39 of 39

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

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

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



    Hi,
    i refer to yellow box highlighted here.
    if H is empty, email cannot be send out.

    It suppose to send out if H is empty


    Quote Originally Posted by DanteAmor View Post
    What do you mean?
    Last edited by harky; Jul 19th, 2019 at 03:53 PM.

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

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

    Quote Originally Posted by harky View Post


    Hi,
    i refer to yellow box highlighted here.
    if H is empty, email cannot be send out.

    It suppose to send out if H is empty

    Try:

    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
        Dim num_err As Variant, sErr As Boolean
    
    
        '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
            sErr = False
            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 wPath <> "" Then
                    If wPattern = "" Then wPattern = "*.*"
                    'If Right(wPath, 1) <> "\" Then wPath = wPath & "\"
                    If Dir(wPath, vbDirectory) <> "" Then
                        wFile = Dir(wPath & wPattern)
                        On Error Resume Next
                        If wFile <> "" Then
                            Do While wFile <> ""
                                .Attachments.Add wPath & wFile
                                num_error = Err.Number
                                If num_error <> 0 Then
                                    wks.Range("I" & i).Value = "No file Attach"
                                    sErr = True
                                End If
                                wFile = Dir()
                            Loop
                        Else
                            wks.Range("I" & i).Value = "wrong file"
                            sErr = True
                        End If
                        On Error GoTo 0
                    Else
                        wks.Range("I" & i).Value = "wrong file path"
                        sErr = True
                    End If
                End If
                If sErr = False Then
                    .Send
                    '.display 'disable display and enable send to send automatically
                    num_error = Err.Number
                    If num_error <> 0 Then
                        wks.Range("I" & i).Value = Err.Description
                    Else
                        wks.Range("I" & i).Value = "email send"
                    End If
                End If
                '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. #33
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    ahhhh It works now! Thanks..

    it work wonder with the status now.. thanks!

    Really appreciate the help here

    Quote Originally Posted by DanteAmor View Post
    Try:

    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
        Dim num_err As Variant, sErr As Boolean
    
    
        '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
            sErr = False
            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 wPath <> "" Then
                    If wPattern = "" Then wPattern = "*.*"
                    'If Right(wPath, 1) <> "\" Then wPath = wPath & "\"
                    If Dir(wPath, vbDirectory) <> "" Then
                        wFile = Dir(wPath & wPattern)
                        On Error Resume Next
                        If wFile <> "" Then
                            Do While wFile <> ""
                                .Attachments.Add wPath & wFile
                                num_error = Err.Number
                                If num_error <> 0 Then
                                    wks.Range("I" & i).Value = "No file Attach"
                                    sErr = True
                                End If
                                wFile = Dir()
                            Loop
                        Else
                            wks.Range("I" & i).Value = "wrong file"
                            sErr = True
                        End If
                        On Error GoTo 0
                    Else
                        wks.Range("I" & i).Value = "wrong file path"
                        sErr = True
                    End If
                End If
                If sErr = False Then
                    .Send
                    '.display 'disable display and enable send to send automatically
                    num_error = Err.Number
                    If num_error <> 0 Then
                        wks.Range("I" & i).Value = Err.Description
                    Else
                        wks.Range("I" & i).Value = "email send"
                    End If
                End If
                '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 19th, 2019 at 04:13 PM.

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

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

    Quote Originally Posted by harky View Post
    ahhhh It works now! Thanks..

    it work wonder with the status now.. thanks!

    Really appreciate the help here
    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

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

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

    Hi Thanks.

    I been using this script.

    Can i add one more function?

    In outlook, there is a SAVE SENT ITEM TO XX folder
    Can i save sent in my ARCHIVE Folder?

    Main: 2019_ARCHIVE
    Subfolder: SendFolder


    Quote Originally Posted by DanteAmor View Post
    I'm glad to help you. Thanks for the feedback.

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

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

    Quote Originally Posted by harky View Post
    Hi Thanks.

    I been using this script.

    Can i add one more function?

    In outlook, there is a SAVE SENT ITEM TO XX folder
    Can i save sent in my ARCHIVE Folder?

    Main: 2019_ARCHIVE
    Subfolder: SendFolder
    I don't know that function. I suggest you create a new thread, we hope someone helps you.
    Regards Dante Amor

  7. #37
    New Member
    Join Date
    Sep 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi guys, new to vba so not sure if the above is actually the answer I'm looking for but it seems the closest to what I've been looking for.
    I send emails out to the same people every month with the same subject and body each month sending 4 pdf reports in sub folders which are relevant to each individual month. This file path element will always be the same \\corp.copart.com\share\UKFinance\Management Accounts\Monthly Management Accts 18_19 but its then the monthly sub folder which then includes another sub folder for each location and then final subfolder containing the reports which I need to select \\corp.copart.com\share\UKFinance\Management Accounts\Monthly Management Accts 18_19\Period 12.19 July\Yard reports\401 Sandy. THESE FOLDERS ONLY CONTAIN THE 4 PDF REPORTS I SEND OUT SO IF I COULD JUST SELECT EVERYTHING IN THE FOLDER THAT WOULD BE GREAT. I don't need a dynamic excel list containing email addresses as these won't but if using an excel spreadsheet like above with file paths. email addresses etc is easier I'm happy to use one. Thanks!

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

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

    If u read, it does what u want..

    attach 1 file or all file from the folder


    Quote Originally Posted by mim92 View Post
    Hi guys, new to vba so not sure if the above is actually the answer I'm looking for but it seems the closest to what I've been looking for.
    I send emails out to the same people every month with the same subject and body each month sending 4 pdf reports in sub folders which are relevant to each individual month. This file path element will always be the same \\corp.copart.com\share\UKFinance\Management Accounts\Monthly Management Accts 18_19 but its then the monthly sub folder which then includes another sub folder for each location and then final subfolder containing the reports which I need to select \\corp.copart.com\share\UKFinance\Management Accounts\Monthly Management Accts 18_19\Period 12.19 July\Yard reports\401 Sandy. THESE FOLDERS ONLY CONTAIN THE 4 PDF REPORTS I SEND OUT SO IF I COULD JUST SELECT EVERYTHING IN THE FOLDER THAT WOULD BE GREAT. I don't need a dynamic excel list containing email addresses as these won't but if using an excel spreadsheet like above with file paths. email addresses etc is easier I'm happy to use one. Thanks!
    Last edited by harky; Aug 20th, 2019 at 09:12 PM.

  9. #39
    New Member
    Join Date
    Sep 2018
    Posts
    3
    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 harky View Post
    If u read, it does what u want..

    attach 1 file or all file from the folder
    Thanks! I just had to change my file path to the folder and not individual files in the folder.

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
  •