Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto E-mail
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 -- Josia ----------------------------------------------------------------------- Josiah's Profile: http://www.excelforum.com/member.php...nfo&userid=453 View this thread: http://www.excelforum.com/showthread.php?threadid=39025 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto E-mail
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 -- Cheers Nigel "Josiah" wrote in message ... I need to know how to write a macro that will automatically send an e-mail of a copy of an excel Worksheet to a particular e-mail address when a button is clicked. What's the best way? (All I really need is the script for the e-mail portion.) Thanks! -- Josiah ------------------------------------------------------------------------ Josiah's Profile: http://www.excelforum.com/member.php...fo&userid=4536 View this thread: http://www.excelforum.com/showthread...hreadid=390259 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto E-mail
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 -- Chuckles12 ----------------------------------------------------------------------- Chuckles123's Profile: http://www.excelforum.com/member.php...fo&userid=1494 View this thread: http://www.excelforum.com/showthread.php?threadid=39025 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto E-mail
See
http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Josiah" wrote in message ... I need to know how to write a macro that will automatically send an e-mail of a copy of an excel Worksheet to a particular e-mail address when a button is clicked. What's the best way? (All I really need is the script for the e-mail portion.) Thanks! -- Josiah ------------------------------------------------------------------------ Josiah's Profile: http://www.excelforum.com/member.php...fo&userid=4536 View this thread: http://www.excelforum.com/showthread...hreadid=390259 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto E-mail
See
http://www.rondebruin.nl/mail/prevent.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Chuckles123" wrote in message ... 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. Do 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 to complete. For my needs, this is not desired. Chuckles123 -- Chuckles123 ------------------------------------------------------------------------ Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948 View this thread: http://www.excelforum.com/showthread...hreadid=390259 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto E-mail
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! -- Josiah ------------------------------------------------------------------------ Josiah's Profile: http://www.excelforum.com/member.php...fo&userid=4536 View this thread: http://www.excelforum.com/showthread...hreadid=390259 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto E-mail
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 -- Chuckles123 ------------------------------------------------------------------------ Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948 View this thread: http://www.excelforum.com/showthread...hreadid=390259 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto E-mail
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 ? -- Regards Ron de Bruin http://www.rondebruin.nl "Chuckles123" wrote in message ... 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 -- Chuckles123 ------------------------------------------------------------------------ Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948 View this thread: http://www.excelforum.com/showthread...hreadid=390259 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto E-mail
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 -- Chuckles12 ----------------------------------------------------------------------- Chuckles123's Profile: http://www.excelforum.com/member.php...fo&userid=1494 View this thread: http://www.excelforum.com/showthread.php?threadid=39025 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto E-mail
Hi
I gather that the CDO links not being available on MSDN is a temporary prob. I hope so -- Regards Ron de Bruin http://www.rondebruin.nl "Chuckles123" wrote in message ... I have not tried one of your examples yet because my server requires 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 temporary prob. Chuckles123 -- Chuckles123 ------------------------------------------------------------------------ Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948 View this thread: http://www.excelforum.com/showthread...hreadid=390259 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto E-mail
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 -- Chuckles12 ----------------------------------------------------------------------- Chuckles123's Profile: http://www.excelforum.com/member.php...fo&userid=1494 View this thread: http://www.excelforum.com/showthread.php?threadid=39025 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto E-mail
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. "Ron de Bruin" wrote: See http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Josiah" wrote in message ... I need to know how to write a macro that will automatically send an e-mail of a copy of an excel Worksheet to a particular e-mail address when a button is clicked. What's the best way? (All I really need is the script for the e-mail portion.) Thanks! -- Josiah ------------------------------------------------------------------------ Josiah's Profile: http://www.excelforum.com/member.php...fo&userid=4536 View this thread: http://www.excelforum.com/showthread...hreadid=390259 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto E-mail
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 "Angus" wrote in message ... 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. "Ron de Bruin" wrote: See http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Josiah" wrote in message ... I need to know how to write a macro that will automatically send an e-mail of a copy of an excel Worksheet to a particular e-mail address when a button is clicked. What's the best way? (All I really need is the script for the e-mail portion.) Thanks! -- Josiah ------------------------------------------------------------------------ Josiah's Profile: http://www.excelforum.com/member.php...fo&userid=4536 View this thread: http://www.excelforum.com/showthread...hreadid=390259 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto insert e-mail addresses | Excel Worksheet Functions | |||
Link Worksheet and Auto send e-mail | Excel Worksheet Functions | |||
Send a auto mail via Notes | Excel Discussion (Misc queries) | |||
Auto e-mail message | Excel Worksheet Functions | |||
How do I permanently deactivate auto e-mail in Excel? | Excel Discussion (Misc queries) |