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

Email Macro
 

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

Email Macro
 

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

Email Macro
 

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

Email Macro
 

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

Email Macro
 

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

Email Macro
 

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