About Us

Gaura215

I have a worksheet, which has the following:

1) Coloum A have the name of the files of the attachments;
2) Coloum B have the email addresses;
3) Coloum C have the path where the attachement are saved;

I want a macro which can email all the attachements with the name as in Coloum A to there corresponding email address as mentioned in Coloum B. All attachments are saved in the same folder, path is mentioned in Coloum C.

I want these mails to be displayed and not sent directly.

All excel/macro gurus, please help.

ExcelBanter AI

Sure, I can help you with that. Here's a step-by-step guide to create a macro that will send emails with attachments based on the information in your worksheet:

Open the Excel workbook that contains the worksheet with the attachments information.
Press Alt + F11 to open the Visual Basic Editor.
In the Visual Basic Editor, go to Insert Module to create a new module.
Copy and paste the following code into the module:

Formula:

Sub SendEmailWithAttachments() Dim OutlookApp As Object Dim OutlookMail As Object Dim i As Integer Dim FilePath As String Dim FileName As String Dim EmailAddress As String Set OutlookApp = CreateObject("Outlook.Application") For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row FilePath = Cells(i, "C").Value FileName = Cells(i, "A").Value EmailAddress = Cells(i, "B").Value Set OutlookMail = OutlookApp.CreateItem(0) With OutlookMail .To = EmailAddress .Subject = "Email with attachment" .Body = "Please find attached the file you requested." .Attachments.Add (FilePath & "\" & FileName) .Display End With Next i Set OutlookMail = Nothing Set OutlookApp = Nothing End Sub
Save the module and close the Visual Basic Editor.
Go back to the worksheet with the attachments information.
Press Alt + F8 to open the Macros dialog box.
Select the SendEmailWithAttachments macro and click on Run.
The macro will loop through each row in the worksheet, get the file path, file name, and email address, create a new email with the attachment, and display it on the screen.
Review each email and click on Send when you're ready to send it.

That's it! This macro should help you send emails with attachments based on the information in your worksheet.

tarquinious

Quote:

Originally Posted by Gaura215

I have a worksheet, which has the following:

1) Coloum A have the name of the files of the attachments;
2) Coloum B have the email addresses;
3) Coloum C have the path where the attachement are saved;

I want a macro which can email all the attachements with the name as in Coloum A to there corresponding email address as mentioned in Coloum B. All attachments are saved in the same folder, path is mentioned in Coloum C.

I want these mails to be displayed and not sent directly.

All excel/macro gurus, please help.

Thankfully I had created similar code for a previous post recently. This macro goes into Outlook, and requires the Excel file with the attachment names and addresses to be open to work.

Firstly, copy and paste the following code into Outlook. You can do this by selecting Tools/Macro/Visual Basic Editor and pasting in the following code.

In the code, you will notice that I have also added in the commands for you to put in a subject and send the emails, but these are remarked out as your request was just to add the address and attachment and not send the email. You can adjust these as required later.

Code:

Sub ReadExcel()
    Dim ExcelObject As Object
    Dim OutlookApp As Outlook.Application
    Dim NewMessage As Outlook.MailItem
    Dim OutlookNamespace As Outlook.NameSpace
    Dim fName, fLoc, eAddress As String
    Dim fNameAddress, fLocAddress, eAddressAddress As String
    
    ' Set up the spreadsheet you want to read
    On Error Resume Next
    Set ExcelObject = GetObject(, "Excel.Application")
    If Not Err.Number = 0 Then
        MsgBox "You need to have Excel running with the appropriate spreadsheet open first", vbCritical, "Excel Not Running"
        End
    End If
    
    ' Read in the data and create a new message with attachment for each Excel entry
    CellRow = 1
    Set OutlookApp = Outlook.Application
    Do Until ExcelObject.Range(fNameAddress) = ""
        fNameAddress = "A" & CellRow
        eAddressAddress = "B" & CellRow
        fLocAddress = "C" & CellRow
        fName = ExcelObject.Range(fNameAddress)
        fLoc = ExcelObject.Range(fLocAddress)
        eAddress = ExcelObject.Range(eAddressAddress)
        fName = fLoc & "\" & fName
        Set OutlookApp = Outlook.Application
        Set NewMessage = OutlookApp.CreateItem(olMailItem)
        Set myAttachments = NewMessage.Attachments
        myAttachments.Add fName
        With NewMessage
            .Recipients.Add eAddress
            .Attachments = fName
            .Display
            ' .Subject = "Put your subject here"
            ' .Send
        End With
        CellRow = CellRow + 1
        fNameAddress = "A" & CellRow
    Loop
End Sub

excelcheck123

hi every one,

the below codes works very well, however, i was just wondering if it is possible to add multiple attachments instead of just one attachment per email. every thing else in the code is fine, just to add additional code to add multiple attachments. for example, in the below macro, outlook picks up information from cell a, cell b and cell c in an excel file containing file name, email address and file path. Now is it possible that multiple file names and be put in cell A to attach to a single email? or any other way to do this. Because I have list of clients to whom i send a set of files to each of them. The below macro only allows me to send one file in one email and i have to send each client multiple emails for each attachment.

please help me in this as this will really solve my problem if the below code can be modified to include multiple attachments.

thanks and best regards,
CJ

Quote:

Originally Posted by tarquinious

Thankfully I had created similar code for a previous post recently. This macro goes into Outlook, and requires the Excel file with the attachment names and addresses to be open to work.

Firstly, copy and paste the following code into Outlook. You can do this by selecting Tools/Macro/Visual Basic Editor and pasting in the following code.

In the code, you will notice that I have also added in the commands for you to put in a subject and send the emails, but these are remarked out as your request was just to add the address and attachment and not send the email. You can adjust these as required later.

Code:

Sub ReadExcel()
    Dim ExcelObject As Object
    Dim OutlookApp As Outlook.Application
    Dim NewMessage As Outlook.MailItem
    Dim OutlookNamespace As Outlook.NameSpace
    Dim fName, fLoc, eAddress As String
    Dim fNameAddress, fLocAddress, eAddressAddress As String
    
    ' Set up the spreadsheet you want to read
    On Error Resume Next
    Set ExcelObject = GetObject(, "Excel.Application")
    If Not Err.Number = 0 Then
        MsgBox "You need to have Excel running with the appropriate spreadsheet open first", vbCritical, "Excel Not Running"
        End
    End If
    
    ' Read in the data and create a new message with attachment for each Excel entry
    CellRow = 1
    Set OutlookApp = Outlook.Application
    Do Until ExcelObject.Range(fNameAddress) = ""
        fNameAddress = "A" & CellRow
        eAddressAddress = "B" & CellRow
        fLocAddress = "C" & CellRow
        fName = ExcelObject.Range(fNameAddress)
        fLoc = ExcelObject.Range(fLocAddress)
        eAddress = ExcelObject.Range(eAddressAddress)
        fName = fLoc & "\" & fName
        Set OutlookApp = Outlook.Application
        Set NewMessage = OutlookApp.CreateItem(olMailItem)
        Set myAttachments = NewMessage.Attachments
        myAttachments.Add fName
        With NewMessage
            .Recipients.Add eAddress
            .Attachments = fName
            .Display
            ' .Subject = "Put your subject here"
            ' .Send
        End With
        CellRow = CellRow + 1
        fNameAddress = "A" & CellRow
    Loop
End Sub

tanveerabd

I would like to thank you for this excellent macro that you posted. it works good and meets my requirements.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro to send email	BAKERSMAN	Excel Discussion (Misc queries)	0	April 2nd 10 01:28 AM
Macro to send email automatically.	Dan Wood	Excel Discussion (Misc queries)	3	December 20th 09 12:19 PM
Macro to send worksheet via email	Julie	Excel Discussion (Misc queries)	2	January 22nd 09 06:41 AM
macro to send email	scheduler	Excel Discussion (Misc queries)	2	October 18th 07 11:20 PM
Can you send a Macro to someone else by email?	Greenback	Excel Discussion (Misc queries)	3	August 2nd 06 01:01 PM

Menu

About Us