Ace My Paper - get your Excel tasks done by experts. - makes your homework shine.


Excel tutorials

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.

Table 1

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"

Table 2

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 send a specific file from a folder to a specified e-mail address from Excel

How to prepare and specify the list of persons and PDF files for sending e-mail via MS Outlook from Excel

Table 3

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.

 How to send PDF files from a specific directory on a specific e-mail addresses from the list in Excel

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.

Sub CopyRangePasteValues()
'Copy-PasteValues VBA Macro
'Copy range P1:Q13 and Paste as Values in range
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

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)

Sub ReturnFormulas()
'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[1],R5C13:R13C14,2,FALSE))"
Selection.AutoFill Destination:=Range("P2:P13"), Type:=xlFillDefault
End Sub

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'.

Sub SendFilesViaEmailList()
'Working in 2000-2010
'source VBA
'modify VBA 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

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

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)

With OutMail
.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
.Attachments.Add FileCell.Value
End If
End If
Next FileCell

.Send 'Or use .Display
End With

Set OutMail = Nothing
End If
Next cell

Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Call ReturnFormulas 'call a VBA macro to restore the formula in the respective range of cells
End Sub

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).

MS Outlook and Excel - Multiple sending email from Excel via MS Outlook program 


When you double-clicking on the message that is checked or perhaps to add another kind of text that looks like the image below.

Example e-mail messages in Microsoft Outlook program with attachment files sent from Excel to list 

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.