e-mail loop macro

daveyc18

Active Member
Joined
Feb 11, 2013
Messages
395
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:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,974
Office Version
2007
Platform
Windows
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
 

daveyc18

Active Member
Joined
Feb 11, 2013
Messages
395
thanks....ill try it soon

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

daveyc18

Active Member
Joined
Feb 11, 2013
Messages
395
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,974
Office Version
2007
Platform
Windows
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.
 

daveyc18

Active Member
Joined
Feb 11, 2013
Messages
395
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:

daveyc18

Active Member
Joined
Feb 11, 2013
Messages
395
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!"


 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,974
Office Version
2007
Platform
Windows
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
 

Forum statistics

Threads
1,082,607
Messages
5,366,591
Members
400,904
Latest member
srija

Some videos you may like

This Week's Hot Topics

Top