Results 1 to 9 of 9

Thread: e-mail loop macro
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2013
    Posts
    360
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default e-mail loop macro

    so I have the filename in column A, and e-mail address in column B and C

    column A
    a_Cass,Mass_Chen,Victor_

    column B
    cass.mass@x.com

    column C

    chen.victor@x.com


    .....

    is it possible to make an e-mail macro that will loop through each filename listed in column A and create an e-mail for that and attach the file name and insert the appropriate e-maill addresses in the "To" and "CC" fields

    so in my example, an e-mail is created, the filename "a_cass,mass_chen,victor_" is attached ; cass.mass@x.com is put in the "To" field'; and chen.victor@x.com is put in the "CC" field ...."subject" = the filename & " leadsheet"

    after that....the macro looks at the next row and creates another e-mail accordingly
    Last edited by daveyc18; Jul 4th, 2019 at 01:48 PM.

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

    Default Re: e-mail loop macro

    Try this:

    Code:
    Sub e_mail_loop()
        Dim i As Long, dam As Object, wfile As String
        For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
            Set dam = CreateObject("Outlook.Application").CreateItem(0)
            dam.To = Range("B" & i).Value
            dam.Cc = Range("C" & i).Value
            dam.Subject = "Write the subject here"
            dam.Body = "Write the body here"
            wfile = ThisWorkbook.Path & "\" & Range("A" & i).Value
            If Dir(wfile) <> "" Then
                dam.Attachments.Add wfile
            End If
            dam.Display
        Next
    End Sub
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Feb 2013
    Posts
    360
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: e-mail loop macro

    thanks....ill try it soon

    i know this is a long shot, but how about incorporating using "use voting buttons" --> "approve; reject"

  4. #4
    Board Regular
    Join Date
    Feb 2013
    Posts
    360
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: e-mail loop macro

    nvm got it

  5. #5
    Board Regular
    Join Date
    Feb 2013
    Posts
    360
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: e-mail loop macro

    how about a check where....it'll loop the the FOLDER containing all the PDF files (attachments)....and then if it's missing from the list in the spreadsheet...the macro warns that the spreadsheet does not contain a filename that's stored in the folder

    note that I want to IGNORE the last 7 characters of the filename in the folder

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

    Default Re: e-mail loop macro

    Quote Originally Posted by daveyc18 View Post
    how about a check where....it'll loop the the FOLDER containing all the PDF files (attachments)....and then if it's missing from the list in the spreadsheet...the macro warns that the spreadsheet does not contain a filename that's stored in the folder

    note that I want to IGNORE the last 7 characters of the filename in the folder
    If you explain clearly how you have the data, maybe I can help you.
    Regards Dante Amor

  7. #7
    Board Regular
    Join Date
    Feb 2013
    Posts
    360
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: e-mail loop macro

    also....in the VBA code...if it does NOT detect the filename in the folder , don't display the e-mail and move onto the next row
    Last edited by daveyc18; Jul 4th, 2019 at 06:04 PM.

  8. #8
    Board Regular
    Join Date
    Feb 2013
    Posts
    360
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: e-mail loop macro

    eg

    next month, there's a file called a_BACK,a_FRONT, but now it's missing in column A because it's new, so I want the macro to warn and say "you're missing a_BACK,a_FRONT in column A; add it!"



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

    Default Re: e-mail loop macro

    Quote Originally Posted by daveyc18 View Post
    also....in the VBA code...if it does NOT detect the filename in the folder , don't display the e-mail and move onto the next row
    Try this

    Code:
    Sub e_mail_loop()
        Dim i As Long, dam As Object, wfile As String
        For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
            wfile = ThisWorkbook.Path & "\" & Range("A" & i).Value
            If Dir(wfile) <> "" Then
                Set dam = CreateObject("Outlook.Application").CreateItem(0)
                dam.To = Range("B" & i).Value
                dam.Cc = Range("C" & i).Value
                dam.Subject = "Write the subject here"
                dam.Body = "Write the body here"
                dam.Display
                dam.Attachments.Add wfile
            End If
        Next
    End Sub
    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
  •