e-mail loop macro

daveyc18

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

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,095
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
440
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
440
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
12,095
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
440
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
440
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
12,095
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,774
Messages
5,482,846
Members
407,365
Latest member
Leah Ashley

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top