Email Macro

Hi

I run XP with Excel 2K and Outlook 2003

I have a distribution list in Outlook that I would like to be contained in a
macro that allows me to run the macro and it opens Outlook with the
distribution list automatically. Alternatively, I would settle for a macro
that just opens Outlook.

Any ideas?


Thanks

John

Everything you need to send emails from Excel can be found at Ron de Bruin's
site.

http://www.rondebruin.nl/sendmail.htm

Note the SendMail add-in which makes it all easy without writing your own
code.


Gord Dibben MS Excel MVP

On Tue, 24 Mar 2009 16:05:04 -0700, John Calder
wrote:

Hi

I run XP with Excel 2K and Outlook 2003

I have a distribution list in Outlook that I would like to be contained in a
macro that allows me to run the macro and it opens Outlook with the
distribution list automatically. Alternatively, I would settle for a macro
that just opens Outlook.

Any ideas?


Thanks

Thanks for your prompt response

I have used the macro supplied by Ron (see below)

************************************************** *

Sub Mail_workbook_1()
'Working in 97-2007
Dim wb As Workbook
Set wb = ActiveWorkbook

If Val(Application.Version) = 12 Then
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" &
vbNewLine & _
"be no VBA code in the file you send. Save the" &
vbNewLine & _
"file first as xlsm and then try the macro again.",
vbInformation
Exit Sub
End If
End If

On Error Resume Next
wb.SendMail ", _
"CPCM DAILY REPORT"
On Error GoTo 0
End Sub

***********************************************

This works fine if I want to send it to once person. However I would like to
send it to 10 different people. Presently I have a distribution list set up
in Outlook with these 10 people. Is there a way that I can include the
distribution list in the Excel macro and if so , how?

Alternateivly, can I put all the email addresses in the macro so that it
goes to all of them?

If so how would I restructure the macro


Thanks


John




"Gord Dibben" wrote:

John

Everything you need to send emails from Excel can be found at Ron de Bruin's
site.

http://www.rondebruin.nl/sendmail.htm

Note the SendMail add-in which makes it all easy without writing your own
code.


Gord Dibben MS Excel MVP

On Tue, 24 Mar 2009 16:05:04 -0700, John Calder
wrote:

Hi

I run XP with Excel 2K and Outlook 2003

I have a distribution list in Outlook that I would like to be contained in a
macro that allows me to run the macro and it opens Outlook with the
distribution list automatically. Alternatively, I would settle for a macro
that just opens Outlook.

Any ideas?


Thanks

Hi John

Click on the Tip link on the webpage

If you use Outlook see also this example with more options
http://www.rondebruin.nl/mail/folder2/mail1.htm

There is also a Tip link on that page


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"John Calder" wrote in message ...
Thanks for your prompt response

I have used the macro supplied by Ron (see below)

************************************************** *

Sub Mail_workbook_1()
'Working in 97-2007
Dim wb As Workbook
Set wb = ActiveWorkbook

If Val(Application.Version) = 12 Then
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" &
vbNewLine & _
"be no VBA code in the file you send. Save the" &
vbNewLine & _
"file first as xlsm and then try the macro again.",
vbInformation
Exit Sub
End If
End If

On Error Resume Next
wb.SendMail ", _
"CPCM DAILY REPORT"
On Error GoTo 0
End Sub

***********************************************

This works fine if I want to send it to once person. However I would like to
send it to 10 different people. Presently I have a distribution list set up
in Outlook with these 10 people. Is there a way that I can include the
distribution list in the Excel macro and if so , how?

Alternateivly, can I put all the email addresses in the macro so that it
goes to all of them?

If so how would I restructure the macro


Thanks


John




"Gord Dibben" wrote:

John

Everything you need to send emails from Excel can be found at Ron de Bruin's
site.

http://www.rondebruin.nl/sendmail.htm

Note the SendMail add-in which makes it all easy without writing your own
code.


Gord Dibben MS Excel MVP

On Tue, 24 Mar 2009 16:05:04 -0700, John Calder
wrote:

Hi

I run XP with Excel 2K and Outlook 2003

I have a distribution list in Outlook that I would like to be contained in a
macro that allows me to run the macro and it opens Outlook with the
distribution list automatically. Alternatively, I would settle for a macro
that just opens Outlook.

Any ideas?


Thanks



__________ Information from ESET Smart Security, version of virus signature database 3960 (20090325) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 3960 (20090325) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Ron

Thanks for your reply.

The original code you supplied worked fine for sending an email to one
person. As i wanted to send to multiple people I went to the "TIP link" as
you suggested and under the heading "Send the mail to more people" I used the
following code:- (just changing the actual email addresses)

..SendMail ", ",
"), _
"DAILY REPORT"

to replace from the original code:-

wb.SendMail ", _
"CPCM DAILY REPORT"

Now when I run the macro I get and error: COMPILE ERROR: Invalid or
unqualified reference

and the .SendMail at the start of the line is highlighted.

Any ideas on what I may be doing wrong?

Thanks

John


"Ron de Bruin" wrote:

Hi John

Click on the Tip link on the webpage

If you use Outlook see also this example with more options
http://www.rondebruin.nl/mail/folder2/mail1.htm

There is also a Tip link on that page


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"John Calder" wrote in message ...
Thanks for your prompt response

I have used the macro supplied by Ron (see below)

************************************************** *

Sub Mail_workbook_1()
'Working in 97-2007
Dim wb As Workbook
Set wb = ActiveWorkbook

If Val(Application.Version) = 12 Then
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" &
vbNewLine & _
"be no VBA code in the file you send. Save the" &
vbNewLine & _
"file first as xlsm and then try the macro again.",
vbInformation
Exit Sub
End If
End If

On Error Resume Next
wb.SendMail ", _
"CPCM DAILY REPORT"
On Error GoTo 0
End Sub

***********************************************

This works fine if I want to send it to once person. However I would like to
send it to 10 different people. Presently I have a distribution list set up
in Outlook with these 10 people. Is there a way that I can include the
distribution list in the Excel macro and if so , how?

Alternateivly, can I put all the email addresses in the macro so that it
goes to all of them?

If so how would I restructure the macro


Thanks


John




"Gord Dibben" wrote:

John

Everything you need to send emails from Excel can be found at Ron de Bruin's
site.

http://www.rondebruin.nl/sendmail.htm

Note the SendMail add-in which makes it all easy without writing your own
code.


Gord Dibben MS Excel MVP

On Tue, 24 Mar 2009 16:05:04 -0700, John Calder
wrote:

Hi

I run XP with Excel 2K and Outlook 2003

I have a distribution list in Outlook that I would like to be contained in a
macro that allows me to run the macro and it opens Outlook with the
distribution list automatically. Alternatively, I would settle for a macro
that just opens Outlook.

Any ideas?


Thanks



__________ Information from ESET Smart Security, version of virus signature database 3960 (20090325) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 3960 (20090325) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Hi John

Add wb before the dot

wb.SendMail ", ", "), _
"DAILY REPORT"


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"John Calder" wrote in message ...
Ron

Thanks for your reply.

The original code you supplied worked fine for sending an email to one
person. As i wanted to send to multiple people I went to the "TIP link" as
you suggested and under the heading "Send the mail to more people" I used the
following code:- (just changing the actual email addresses)

.SendMail ", ",
"), _
"DAILY REPORT"

to replace from the original code:-

wb.SendMail ", _
"CPCM DAILY REPORT"

Now when I run the macro I get and error: COMPILE ERROR: Invalid or
unqualified reference

and the .SendMail at the start of the line is highlighted.

Any ideas on what I may be doing wrong?

Thanks

John


"Ron de Bruin" wrote:

Hi John

Click on the Tip link on the webpage

If you use Outlook see also this example with more options
http://www.rondebruin.nl/mail/folder2/mail1.htm

There is also a Tip link on that page


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"John Calder" wrote in message ...
Thanks for your prompt response

I have used the macro supplied by Ron (see below)

************************************************** *

Sub Mail_workbook_1()
'Working in 97-2007
Dim wb As Workbook
Set wb = ActiveWorkbook

If Val(Application.Version) = 12 Then
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" &
vbNewLine & _
"be no VBA code in the file you send. Save the" &
vbNewLine & _
"file first as xlsm and then try the macro again.",
vbInformation
Exit Sub
End If
End If

On Error Resume Next
wb.SendMail ", _
"CPCM DAILY REPORT"
On Error GoTo 0
End Sub

***********************************************

This works fine if I want to send it to once person. However I would like to
send it to 10 different people. Presently I have a distribution list set up
in Outlook with these 10 people. Is there a way that I can include the
distribution list in the Excel macro and if so , how?

Alternateivly, can I put all the email addresses in the macro so that it
goes to all of them?

If so how would I restructure the macro


Thanks


John




"Gord Dibben" wrote:

John

Everything you need to send emails from Excel can be found at Ron de Bruin's
site.

http://www.rondebruin.nl/sendmail.htm

Note the SendMail add-in which makes it all easy without writing your own
code.


Gord Dibben MS Excel MVP

On Tue, 24 Mar 2009 16:05:04 -0700, John Calder
wrote:

Hi

I run XP with Excel 2K and Outlook 2003

I have a distribution list in Outlook that I would like to be contained in a
macro that allows me to run the macro and it opens Outlook with the
distribution list automatically. Alternatively, I would settle for a macro
that just opens Outlook.

Any ideas?


Thanks



__________ Information from ESET Smart Security, version of virus signature database 3960 (20090325) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 3960 (20090325) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 3961 (20090325) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 3961 (20090325) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Ron

Thanks a lot for your help.


John


"Ron de Bruin" wrote:

Hi John

Add wb before the dot

wb.SendMail ", ", "), _
"DAILY REPORT"


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"John Calder" wrote in message ...
Ron

Thanks for your reply.

The original code you supplied worked fine for sending an email to one
person. As i wanted to send to multiple people I went to the "TIP link" as
you suggested and under the heading "Send the mail to more people" I used the
following code:- (just changing the actual email addresses)

.SendMail ", ",
"), _
"DAILY REPORT"

to replace from the original code:-

wb.SendMail ", _
"CPCM DAILY REPORT"

Now when I run the macro I get and error: COMPILE ERROR: Invalid or
unqualified reference

and the .SendMail at the start of the line is highlighted.

Any ideas on what I may be doing wrong?

Thanks

John


"Ron de Bruin" wrote:

Hi John

Click on the Tip link on the webpage

If you use Outlook see also this example with more options
http://www.rondebruin.nl/mail/folder2/mail1.htm

There is also a Tip link on that page


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"John Calder" wrote in message ...
Thanks for your prompt response

I have used the macro supplied by Ron (see below)

************************************************** *

Sub Mail_workbook_1()
'Working in 97-2007
Dim wb As Workbook
Set wb = ActiveWorkbook

If Val(Application.Version) = 12 Then
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" &
vbNewLine & _
"be no VBA code in the file you send. Save the" &
vbNewLine & _
"file first as xlsm and then try the macro again.",
vbInformation
Exit Sub
End If
End If

On Error Resume Next
wb.SendMail ", _
"CPCM DAILY REPORT"
On Error GoTo 0
End Sub

***********************************************

This works fine if I want to send it to once person. However I would like to
send it to 10 different people. Presently I have a distribution list set up
in Outlook with these 10 people. Is there a way that I can include the
distribution list in the Excel macro and if so , how?

Alternateivly, can I put all the email addresses in the macro so that it
goes to all of them?

If so how would I restructure the macro


Thanks


John




"Gord Dibben" wrote:

John

Everything you need to send emails from Excel can be found at Ron de Bruin's
site.

http://www.rondebruin.nl/sendmail.htm

Note the SendMail add-in which makes it all easy without writing your own
code.


Gord Dibben MS Excel MVP

On Tue, 24 Mar 2009 16:05:04 -0700, John Calder
wrote:

Hi

I run XP with Excel 2K and Outlook 2003

I have a distribution list in Outlook that I would like to be contained in a
macro that allows me to run the macro and it opens Outlook with the
distribution list automatically. Alternatively, I would settle for a macro
that just opens Outlook.

Any ideas?


Thanks



__________ Information from ESET Smart Security, version of virus signature database 3960 (20090325) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 3960 (20090325) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 3961 (20090325) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 3961 (20090325) __________

The message was checked by ESET Smart Security.

http://www.eset.com