How to send email to list of individuals

  • Thread starter Thread starter jason
  • Start date Start date
J

jason

I have the below code to generate an email, I would like to add a To: block
and take the list of receipiants from an excel spreadsheet that updates
daily. How can this be done.

Sub Macro1()



Dim olApp As Outlook.Application

Dim olMsg As Outlook.MailItem



Set olApp = Outlook.Application

Set olMsg = olApp.CreateItem(olMailItem)



With olMsg



.Subject = "Hello world"

.Display

.Body = "Hello, here is my email!" & .Body

End With



Set olMsg = Nothing

Set olApp = Nothing

End Sub
 
Assuming, Excel is already running, and the first address is in cell "a1":

Dim Xl As Excel.Application
Dim Ws As Excel.Worksheet
Dim Rn As Excel.Range

Set Xl = GetObject(, "Excel.Application")
Set Ws = Xl.Workbooks("Mappe1.xls").Worksheets(1)

Set Rn = Ws.Range("a1")

Now you can read Rn.Value for the first address, then loop through the rows
with the Offset function until the Value="".

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>



Am Tue, 10 Feb 2009 08:45:02 -0800 schrieb jason:
 
The loop could look like this:

While Rn.Value<>""
.Recipients.Add Rn.Value
Set Rn=Rn.Offset(1,0)
Wend

Add the code to the With olMsg block.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>


Am Tue, 10 Feb 2009 09:41:05 -0800 schrieb jason:
 
Here is the code that I have, but I keep getting an error that says "user
defined type not defined. Any help would be appreciated

Sub Macro1()
Dim myOlApp As New Outlook.Application
Dim myItem As Outlook.MailItem


Dim olApp As Outlook.Application

Dim olMsg As Outlook.MailItem




Set olApp = Outlook.Application

Set olMsg = olApp.CreateItem(olMailItem)



With olMsg

Dim Xl As Excel.Application
Dim Ws As Excel.Worksheet
Dim Rn As Excel.Range

Set Xl = GetObject(, "Excel.Application")
Set Ws = Xl.Workbooks("book1.xls").Worksheets(1)

Set Rn = Ws.Range("a1")
While Rn.Value <> ""
.Recipients.Add Rn.Value
Set Rn = Rn.Offset(1, 0)
Wend

.CC = "xxx"
.Subject = "xxx"

.Display
.Body = "xxx" & .Body
.Body = "xxx" & .Body

.Attachments.Add "xxx"


End With
End Sub
 
If you get the error for one of the Excel objects, you need to add a
reference to the Microsoft Excel x Object Library via Tools/References.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>


Am Fri, 13 Feb 2009 07:53:01 -0800 schrieb jason:
 
Thanks for all the help it works great. One last question, how can I put the
reciepents in the BCC box instead of the TO box with this code
 
Dim Recip as Outlook Recipient

Set Recip=.Recipients.Add(...)
Recip.Type=olBCC

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>


Am Fri, 13 Feb 2009 09:33:02 -0800 schrieb jason:
 
Back
Top