Send outlook emails with message in cells to individual emails associated with them

abinayan

New Member
Joined
Jan 28, 2011
Messages
14
Hi everyone,

Basically I have a data sheet with a list individuals in one column and individual messages in the next and their associated email addresses in another. I have an excel VBA code which sends the whole worksheet as an email attachment to a specific address. Is there a way to modify the code to send individual emails with individual messages via outlook?

I would basically like the macro to run through each ID and select and copy the data range associated to it as the body of the message and then send it to the corresponding email address. The datarange varies for each ID and I have more than a 1000 of them.
The code I have currently which sends the whole worksheet as an attachment is as follows:
code:
Sub email()
Dim myOutlook As Object
Dim myMailItem As Object
Dim FName As String
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
FName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
With otlNewMail
.To = "xxx@yyy.com"
.CC = ""
.Subject = "Test"
.Body = "Test"
.Attachments.Add FName
.DeferredDeliveryTime = Range("A1")
.Send
End With

Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
End Sub




Thanks guys.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Watch MrExcel Video

Forum statistics

Threads
1,102,774
Messages
5,488,774
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top