Auto E-mail

[Josiah]

I need to know how to write a macro that will automatically send a
e-mail of a copy of an excel Worksheet to a particular e-mail addres
when a button is clicked.

What's the best way? (All I really need is the script for the e-mai
portion.)

Thanks

 

[Nigel]

Hi, Here is solution you might like to try or adapt. It has two procedures
the first MailSheets scans each worksheet in the activeworkbook and if cell
B1 has a value (string > 0) it creates a copy of the worksheet as a new
workbook (same name as the worksheet) and mails it to the contents of B1.

The second part is the SendMail procedure that takes the email address and
sends the workbook just created. Finally control returns to the first
procedure and the workbook created is then deleted. Only word of caution,
it presumes that the email address in B1 is valid! and that the creation of
a new workbook of the same name as the sheet is acceptable. If it already
exists you get a warning.

You need to add a reference to Outlook in the VBE.

Sub MailSheets()
Dim sh As Worksheet, email As String, shName As String
Application.ScreenUpdating = False
For Each sh In Worksheets
email = sh.Range("B1").Value
shName = sh.Name
If Len(email) > 0 Then
sh.Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & shName & ".xls"
Call SendMail(email)
ActiveWorkbook.Close False
Kill ThisWorkbook.Path & "\" & shName & ".xls"
End If
Next sh
Application.ScreenUpdating = True
End Sub

' Mailing procedure
Sub SendMail(eMadd As String)
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = eMadd
.CC = ""
.BCC = ""
.Subject = "WorkSheet Mailing"
.Body = "This is an automated email with the attached worksheet"
.Attachments.Add ActiveWorkbook.FullName
'.DeleteAfterSubmit = True ' use this option if you do not want an
entry in the senders sent mail folder
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[Chuckles123]

Nigel,

Doesn't your 'With OutMail.Send' result in the following pop-up:?

"A program is trying to automatically send e-mail on your behalf. D
you want to allow this? Yes-No-Help". At the bottom of the pop-up
there is a dark blue expanding progress bar that takes a few seconds t
complete.

For my needs, this is not desired.

Chuckles12

 

[Ron de Bruin]

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

 

[Ron de Bruin]

See
http://www.rondebruin.nl/mail/prevent.htm

 

[Josiah]

Is there a way that I can choose a specific e-mail program to use in the
coding (instead of Outlook)?

Thanks for the replies. This is very helpful!

 

[Chuckles123]

Ron,

In your article titled "Sending mail from Excel with CDO", there is a
link to an MSDN Page -- when I click on it, the pop-up says "Page
Cannot Be Found". Do you have an updated link?

When doing a Google search on 'CDOSYS mail', I came across several
references to:
- VBScript;
- saving code in files with an '.asp' extension; and
- use of '<%' and '%>' in VBScript files, I guess.

This is all Greek to me; do I need to know any of this to send e-mails
using CDOSYS?

TIA,
Chuckles123

 

[Ron de Bruin]

Hi Chuckles123

All CDO links are not working on MSDN on this moment I see.

This is all Greek to me; do I need to know any of this to send e-mails
using CDOSYS?

No
Have you try a code example from my page ?

 

[Chuckles123]

I have not tried one of your examples yet because my server require
username and password; this is why I am asking about the MSDN Page.

I gather that the CDO links not being available on MSDN is a temporar
prob.

Chuckles12

 

[Ron de Bruin]

Hi

I gather that the CDO links not being available on MSDN is a temporary
prob.

I hope so

 

[Chuckles123]

THE CODE BELOW IS NOT WORKING.

I GET: "Run time error '-2147220973 (80040213)':
The transport failed to connect to the server."

HERE IS MY CODE:

Dim iConf As Object
Dim strbody As String
Dim Flds As Variant

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

iConf.Load -1 'CDO Source Defaults
Set Flds = iConf.Fields

With Flds

.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenicate"
= 1

.Item("http://schemas.microsoft.com/cdo/configuration/sendusername")
"USERNAME"

.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword")
"PASSWORD"
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing")
2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver"
= "EMAILADDRESS_1"

.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"
= 10

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport"
= 25
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl"
= False
.Update
End With

strbody = "Hi there"

With iMsg
Set .Configuration = iConf
.To = "EMAILADDRESS_2"
.CC = ""
.BCC = ""
.From = "EMAILADDRESS_1"
.Subject = "Important Message"
.TextBody = strbody
.Send
End With

Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing

End Sub

Any comments?
Chuckles12

 

[Guest]

Dear Ron,

I use your code "Sending mail from Excel with CDO" and when it is reading
following code it comes a alert message. How to disable the alert?

TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") &
".htm"
Nwb.SaveAs TempFile, xlHtml

One more question, when I send pivot table with your code, I found the mail
size is very big (1 to 2 mb), is it possible to make it slim?

Last question, can you make a forum at your homepage? I use a lot of your
stuff and they work perfect, thanks.

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

 

[Ron de Bruin]

Hi Angus

This is one line (is your line red in the VBA editor)

TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") &
".htm"


Check out the usedrange of the sheet
http://www.contextures.com/xlfaqApp.html#Unused

Last question, can you make a forum at your homepage? I use a lot of your
stuff and they work perfect, thanks.

Glad you think the site is useful
We can use the newsgroup to discuss things



--
Regards Ron de Bruin
http://www.rondebruin.nl

Dear Ron,

I use your code "Sending mail from Excel with CDO" and when it is reading
following code it comes a alert message. How to disable the alert?

TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") &
".htm"
Nwb.SaveAs TempFile, xlHtml

One more question, when I send pivot table with your code, I found the mail
size is very big (1 to 2 mb), is it possible to make it slim?

Last question, can you make a forum at your homepage? I use a lot of your
stuff and they work perfect, thanks.