Excel VBA Macro to send emails & attachments

[Jordan2]

Hi,
I need some help in putting together a macro which matches the excel files in a folder to the name in the main folder and send only those ones out.

For example I have 10 excel files in my folder, let's say on the desktop:
Each folder is named 'Person 1' through to 'Person 10' and in the file it's got everyone's contact information.
How would I create this and produce only 10 emails with their 10 individual attachments?

Thanks.

 

[SeniorNewbie]

Hi Jordan,
1st question: Are there big differences in the data in the ten files, or is it only to be personalized in some lines?
2nd question: Here you're asking only for a solution to send out mails. What's about printer destinations? A delivery list for the outgoing goods department, an invoice for the accounting and a report for the boss?
Let me learn!

 

[Jordan2]

Hi Jordan,
1st question: Are there big differences in the data in the ten files, or is it only to be personalized in some lines?
2nd question: Here you're asking only for a solution to send out mails. What's about printer destinations? A delivery list for the outgoing goods department, an invoice for the accounting and a report for the boss?
Let me learn!

Hi,
The differences in data aren't really big at all, it's a table of 7 columns with anywhere from 1 to 20 rows tops, consisting of the headings below:

Payer No.

Payer Name

Customer No.

Customer Name

Area

Number

Name

All the files saved in destFolder = Environ("USERPROFILE") & "\Desktop\Debtors\Reports\"
The files are named as the name column, so if there's 4 customers under the same name, it'll name it as that and pair all the data to that one workbook.

I was hoping that the master file, which I've put all internal contact information to contact the customers can be vlookup'd and then put the correct persons email in if that's a thing to then create personalised email for say those 10 customers. Does that make sense?

 

[Jordan2]

Hi Jordan,
1st question: Are there big differences in the data in the ten files, or is it only to be personalized in some lines?
2nd question: Here you're asking only for a solution to send out mails. What's about printer destinations? A delivery list for the outgoing goods department, an invoice for the accounting and a report for the boss?
Let me learn!

Hi,
I've added 4 attachments in the process of what I want achieved in terms of the email, if that makes sense?

 

[SeniorNewbie]

ok - may way to handle something like that would be:
Create in XL for every manager/reciepient an own sheet including the mail address, subject etc in e.g. A1to Ax.
Then run a routine through all this sheets transferring the mail address, subject, body to outlook and done.

Here you can find a little tool that distributes single sheets to different printers, what may help you already to go through the sheets. theDistributor++ (next version, not yet online, but I'll let you know) will include mail addresses as well. Sorry, but it's a German forum, so you may need help by www.deepl.com.
have fun!

 

[Jordan2]

ok - may way to handle something like that would be:
Create in XL for every manager/reciepient an own sheet including the mail address, subject etc in e.g. A1to Ax.
Then run a routine through all this sheets transferring the mail address, subject, body to outlook and done.

Here you can find a little tool that distributes single sheets to different printers, what may help you already to go through the sheets. theDistributor++ (next version, not yet online, but I'll let you know) will include mail addresses as well. Sorry, but it's a German forum, so you may need help by www.deepl.com.
have fun!

Ok will give that a try thanks

 

[SeniorNewbie]

As promised the next verion is online now. It offers a distribution list for hardcopies, PDF, OneNote and Outlook.
Have fun!

Senior Newbie

 

[Jordan2]

Thanks, will give it a try when I get round to it

 

[Jordan2]

As promised the next verion is online now. It offers a distribution list for hardcopies, PDF, OneNote and Outlook.
Have fun!

Senior Newbie

Unfortunately as a work computer I'm unable to download this software.

Would you be able to assist with this code which I have? Currently I have an issue with the 'loop through files in folder section.


Dim folderPath As String, fileName As String, fullName As String
Dim recipientEmail As String, ccEmail As String
Dim wsContacts As Worksheet, outlookApp As Object, newEmail As Object

' Set the folder path
folderPath = "C:\Users\Reports"

' Set the worksheet object
Set wsContacts = ThisWorkbook.Worksheets("contacts")

' Create Outlook application object
Set outlookApp = CreateObject("Outlook.Application")

' Loop through files in the folder
For Each fileName In Dir(folderPath & "\*.xlsx")
fullName = folderPath & "\" & fileName

' Check if file name matches contact name
For Row = 2 To wsContacts.Cells(Rows.Count, "I").End(xlUp).Row
If wsContacts.Cells(Row, "I").Value & ".xlsx" = fileName Then
recipientEmail = wsContacts.Cells(Row, "G").Value
ccEmail = wsContacts.Cells(Row, "H").Value

' Create a new email
Set newEmail = outlookApp.CreateItem(0)

' Set email properties
With newEmail
.SentOnBehalfOfName = "noreply@company.co.uk"
.To = recipientEmail
.CC = ccEmail
.Subject = "RE: Inactive Debtor"
.Body = "Dear Colleagues" & "<br>" & "Please find attached." & "<br>" & "Kind regads," & "<br>" & "[Insert Department]"
.Attachments.Add fullName
.Send
End With

Exit For ' Move to next file after finding a match
End If
Next Row
Next fileName

Set wsContacts = Nothing
Set outlookApp = Nothing

End Sub

 

[SeniorNewbie]

You've got no PC at home, no USB stick to transfer my little file? I'm using skype for my individual file transfer ...