How to specific e-mail address to join a specific PDF file from folder to send from Excel through the Microsoft Outlook program
In this Excel Example Tutorial I will show how we can with the help of Excel formulas and VBA macros join and then send as an attachment to certain PDF files to a specified e-mail address from the list to Excel Workbook via the Microsoft Outlook program.
In the picture below you see the situation with the layout table (Table1 and Table2, the range of data) that we have as a database from which we pull specific data and prepare a specific PDF file (this file as an attachment can also be *.TXT, *.CSV, *.DOC, etc).
Required! Read the comments in the cells on the worksheet.
In the range of cells 'A5:C13' there is a list, ie. name of the person and the associated e-mail address of the person concerned where we want to e-mail send to a specific file (PDF, DOC, CSV, etc). These files are located in the path "C:\Temp"
In the range of cells 'E5:G13' is a list of the name and surname of the person and the associated PDF file you want as an attachment in e-mail program Microsoft Outlook to send to the person concerned.
How to prepare and specify the list of persons and PDF files for sending e-mail via MS Outlook from Excel
For more texts see the image below.
Our task is to "Table 3" in the range of cells 'K5:N13' to prepare a list of all persons who want to send an e-mail message and join each person a specific PDF file as an attachment, which is located in a folder on your hard drive (C:\Temp )
So, in "Table 3" in columns 'K:L' we enter first and last names of certain persons. These two data are conditions under which we must return to the other results (path/file name and e-mail address)
In order to facilitate the resolution of the task you I'm in "Table 1 and Table 2" added extra column 'A' and 'F' in which I concatenate the name because in further calculations it helps me in the VLOOKUP formula searches for, because then the unique information. Of course you can hide these columns. Here the problem arises only if we have two people with the same name but that can be solved by additional marks with a surname like ordinal number.
The formula in cell 'A5' is as follows: the formula is copied down.
The formula in cell "F5" is as follows: the formula is copied down.
Furthermore, in the column 'M' put a formula that will give us for each person to show the path to the file that we specified in "Table 2". Given that we know which folder contains files automatically trajectory can concatenate with other formulas that will find a certain file in column "I" in "Table 2" and join this path.
The formula in cell 'M5' is as follows: copy the formula down. (formulas should be in one line of code)
In the column 'N' as the results are e-mail addresses that are for conditions in 'K: L' columns returned e-mail address from "Table 1"
The formula in cell 'N5' is as follows: copy the formula down. (formulas should be in one line of code)
In the picture below you will see that I am in the columns 'M' and 'N', use Conditional Formatting as a warning that we enter some information to calculate the planned requirements in the application.
Column 'M': So, if are in column 'M' appears in the background color of a cell, it means that in "Table 2" is not a person and its associated files.
The column 'N': If the column "N" appears in red cells that means for the person in the "Table 2", there is an associated file but there is no e-mail address in the "Table 1".
List of all the e-mail address and the associated PDF files prepared for sending e-mail via MS Outlook.
After entry of certain persons in the columns 'K:L' (first and last names) in "Table 3" Excel automatically prepares a list of e-mail address and the associated PDF files to send via MS Outlook program.
The formula in cell 'P2' is as follows: the formula is copied down.
ARRAY formula in cell 'Q2' is as follows: copy the formula down.
In this way, we have created a list of e-mail address and the associated PDF file and ready to send via Microsoft Outlook program.
NOTE! You notice in the image below in columns 'P' and 'Q' that in one row do not have an email address but we have the PDF format file. Notwithstanding this situation we can easily send only completed lines ie. those cells that have the corresponding elements. It is this situation I simulate to notice potential problems. To solve this problem in the situation in the picture below, simply wipe name ('mmm'/'mmm') in the cells 'K9' and 'L9' because our red cells to 'N9' warns of the problem.
Send an e-mail message from Excel
Once you have prepared a list of all the e-mail address of the file as an attachment it is necessary to do the final step, ie, to send all the files to the corresponding e-mail addresses via MS Outlook program.
Sending e-mail messages using VBA elaborated Excel MVP RondeBruin so I used the VBA macro to automate sending more e-mails from Excel once. For automated sending more e-mail messages in your Excel VBE need to a few VBA macros and create VBA Button over by clicking automatically send all email messages.
This automation of sending e-mail messages with associated attachment files works as follows. All VBA macros are in general 'Module1'.
Sub Send Files Via MailList () 'main VBA macro to send e-mail from Excel
Call CopyRangePasteValues ' VBA macro that copies the range and pasted as values/text
...... main VBA code to send e-mail ........ 'VBA macro that sends e-mail from Excel to the address from the list
Call ReturnFormulas 'VBA macro that returns the formulas back into the range of cells identical to the state before starting the main procedures
End Sub 'end procedure
VBA macro to the Copy/Paste_Special => Paste Values
In order to automate the sending of e-mails from Excel using VBA macros, it is necessary to list in the table to send, remove formulas to see them Excel as text data. With this auxiliary VBA macro below we copy the data range 'Q2:Q13' and automatically return again as a text (Paste Values) on the same range of data. This VBA macro later invite inside the main VBA macro to send an e-mail message from Excel.
'Copy-PasteValues VBA Macro
'Copy range P1:Q13 and Paste as Values in range
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Application.CutCopyMode = False
VBA macro that returns the formula back in the specified range of cells
Another auxiliary VBA macro returns the formula back to the specified range of cells 'Q2:Q13' so that we are ready again for a new list and send a new e-mail messages with other attachment. (formula must be entered in one line code)
'VBA macro for put formulas again in the P/R column
Selection.FormulaArray = _
Selection.AutoFill Destination:=Range("Q2:Q13"), Type:=xlFillDefault
ActiveCell.FormulaR1C1 = "=HYPERLINK(VLOOKUP(RC,R5C13:R13C14,2,FALSE))"
Selection.AutoFill Destination:=Range("P2:P13"), Type:=xlFillDefault
Main VBA macro to send multiple email messages from Excel
And finally here's a VBA macro that sends messages to all email addresses in column 'P'.
'Working in 2000-2010
'source VBA http://www.rondebruin.nl
'modify VBA http://ic-ims.com Call CopyRangePasteValues 'call a VBA macro to copy/paste values in range
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range, FileCell As Range, rng As Range
.EnableEvents = False
.ScreenUpdating = False
Set sh = Sheets("Sheet1") 'the name of a worksheet that contains a list of e-mail address
Set OutApp = CreateObject("Outlook.Application")
For Each cell In sh.Columns("P").Cells.SpecialCells(xlCellTypeConstants)
'Enter the file names in the Q:Z column in each row
Set rng = sh.Cells(cell.Row, 1).Range("Q1:R1") 'range with list files
If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)
.to = cell.Value
.Subject = "Report for the month of February 2015" 'predefined subject text
.Body = "Hello, I am sending you my report " & cell.Offset(0, -1).Value 'predefined body text
For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
If Trim(FileCell) <> "" Then
If Dir(FileCell.Value) <> "" Then
.Send 'Or use .Display
Set OutMail = Nothing
Set OutApp = Nothing
.EnableEvents = True
.ScreenUpdating = True
Call ReturnFormulas 'call a VBA macro to restore the formula in the respective range of cells
Of course, that would continually run a VBA macro via the ALT+F8 key combination on your keyboard, you can create a VBA Button and use it to run the VBA macro. After starting the VBA macros in your Microsoft Outlook program in the Outbox box have as many messages as e-mail address in the list (in this case it is three e-mail messages that contain attachments to send).
When you double-clicking on the message that is checked or perhaps to add another kind of text that looks like the image below.
And finally, I can only say the following. I am convinced that there is an easier way to solve the problem or task, my idea for send specific PDF files from folder to specific e-mail addresses from Excel list via MS Outlook is this way. And of course for those who want to try out the example in this tutorial to send specific PDF file to a specific e-mail from Excel via MS Outlook, here is link to a DOWNLOAD example file from this tutorial. Copy these VBA Macros in your Excel VBE but pay attentio for notice for one line code.