Send an email attachment (pdf file) from Excel 2003 using Outlook Express
Hi
I use Windows XP Pro, Excel 2003, Outlook Express 6.xxx. Here is what I am trying to do. I have an excel workbook, which I am trying to convert to PDF and then attach the saved PDF version of the excel sheet to an Outlook Express mail and send to different Email address, from within the Excel Workbook. I will tackle converting to PDF part later. At the moment I am trying to develop code that will create a new Email message with a PDF file as attachment in Outlook Express. I have been reading around various newsgroups. Two codes which I have come across in this forum seem close to what I want to do: Method 1. -------------- Sub MailOXpress2() Dim dest$, sujet$, texte$ Dim Rep Application.ScreenUpdating = False Rep = "C:\Documents and Settings\xxxx\Desktop\file.pdf" 'file to attach dest = " 'email address sujet = "Send a mail from XL" texte = "Send with Outlook Express from Excel" Shell "C:\Program Files\Outlook Express\msimn.exe " & _ "/mailurl:mailto:" & dest & _ "?subject=" & sujet & _ "&Body=" & texte & ", 3", vbMaximizedFocus 'send with attached workbook "Rep" SendKeys "%I" & "p" & Rep & "~" & "%s" Application.ScreenUpdating = True End Sub ------------ 2 problems he 1. While a new outlook express message correctly addressed is created, the file does not get attached. 2. I would have liked to see the message automatically go to the outbox of the Outlook Express, rather than the user clicking 'Send' to put the message. Can somebody help me address these two points? Method 2 ------------- I saw this at Ron's site. I don't know if I did the customization of the code properly... Sub CDO_Send_ActiveSheet() Dim iMsg As Object Dim iConf As Object ' Dim Flds As Variant Application.ScreenUpdating = False 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/sendusing") = 2 ' ..Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "Fill in your SMTP server here" ' ..Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 ' .Update ' End With With iMsg Set .Configuration = iConf .to = " .CC = "" .BCC = "" .From = """Me"" " .Subject = "This is a test" .TextBody = "Hi there" .AddAttachment "C:\Documents and Settings\xxxx\Desktop\file.pdf" .Send End With Set iMsg = Nothing Set iConf = Nothing Application.ScreenUpdating = True End Sub -------- This throws an error at the last step (.Send) saying SMPT server not found etc.. I am connected to internet via ADSL at the time of trying it. Can someone please help me? |
Send an email attachment (pdf file) from Excel 2003 using Outlook Express
Ron's method does work, I suspect you are not setting the CDO configuation
properly....something like Set iConf = CreateObject("CDO.Configuration") iConf.Load -1 ' CDO Source Defaults Set Flds = iConf.Fields With Flds .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.your servername" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "your user id" .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "your password" .Update End With -- Cheers Nigel wrote in message oups.com... Hi I use Windows XP Pro, Excel 2003, Outlook Express 6.xxx. Here is what I am trying to do. I have an excel workbook, which I am trying to convert to PDF and then attach the saved PDF version of the excel sheet to an Outlook Express mail and send to different Email address, from within the Excel Workbook. I will tackle converting to PDF part later. At the moment I am trying to develop code that will create a new Email message with a PDF file as attachment in Outlook Express. I have been reading around various newsgroups. Two codes which I have come across in this forum seem close to what I want to do: Method 1. -------------- Sub MailOXpress2() Dim dest$, sujet$, texte$ Dim Rep Application.ScreenUpdating = False Rep = "C:\Documents and Settings\xxxx\Desktop\file.pdf" 'file to attach dest = " 'email address sujet = "Send a mail from XL" texte = "Send with Outlook Express from Excel" Shell "C:\Program Files\Outlook Express\msimn.exe " & _ "/mailurl:mailto:" & dest & _ "?subject=" & sujet & _ "&Body=" & texte & ", 3", vbMaximizedFocus 'send with attached workbook "Rep" SendKeys "%I" & "p" & Rep & "~" & "%s" Application.ScreenUpdating = True End Sub ------------ 2 problems he 1. While a new outlook express message correctly addressed is created, the file does not get attached. 2. I would have liked to see the message automatically go to the outbox of the Outlook Express, rather than the user clicking 'Send' to put the message. Can somebody help me address these two points? Method 2 ------------- I saw this at Ron's site. I don't know if I did the customization of the code properly... Sub CDO_Send_ActiveSheet() Dim iMsg As Object Dim iConf As Object ' Dim Flds As Variant Application.ScreenUpdating = False 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/sendusing") = 2 ' .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "Fill in your SMTP server here" ' .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 ' .Update ' End With With iMsg Set .Configuration = iConf .to = " .CC = "" .BCC = "" .From = """Me"" " .Subject = "This is a test" .TextBody = "Hi there" .AddAttachment "C:\Documents and Settings\xxxx\Desktop\file.pdf" .Send End With Set iMsg = Nothing Set iConf = Nothing Application.ScreenUpdating = True End Sub -------- This throws an error at the last step (.Send) saying SMPT server not found etc.. I am connected to internet via ADSL at the time of trying it. Can someone please help me? |
Send an email attachment (pdf file) from Excel 2003 using Outlook Express
Ok..some developments...
I just got Ron's method to work. It work the way I wanted it...Thanks Ron. I don't know what exactly fixed the problem..these things were done... 1. Gave Excel permission to send mail in ZoneAlarm 2. Changed the From Email address from to my actual Email address (of my SMTP server). 3. In Outlook Express, ToolsAccountsMailPropertiesServersOutgoing Mail Server, unchecked 'My server require Authentication'. Now luckily I have two SMTP servers, one requiring authentication and one that do not. How do I give a password authentication through VBA if I were to use the SMPT server which need authentication... Shall appreciate if someone can also help why first method is failing... Thanks in advance |
Send an email attachment (pdf file) from Excel 2003 using Outlook Express
see my other reply.....
-- Cheers Nigel wrote in message oups.com... Ok..some developments... I just got Ron's method to work. It work the way I wanted it...Thanks Ron. I don't know what exactly fixed the problem..these things were done... 1. Gave Excel permission to send mail in ZoneAlarm 2. Changed the From Email address from to my actual Email address (of my SMTP server). 3. In Outlook Express, ToolsAccountsMailPropertiesServersOutgoing Mail Server, unchecked 'My server require Authentication'. Now luckily I have two SMTP servers, one requiring authentication and one that do not. How do I give a password authentication through VBA if I were to use the SMPT server which need authentication... Shall appreciate if someone can also help why first method is failing... Thanks in advance |
Send an email attachment (pdf file) from Excel 2003 using Outlook Express
Nigle
Thanks for the quick reply...It worked even with the part of the code you mentioned commentated out. Still let me see I can use that to give authentication to the SMTP server... Thanks. On Nov 16, 2:51 pm, "Nigel" wrote: Ron's method does work, I suspect you are not setting the CDO configuation properly....something like Set iConf = CreateObject("CDO.Configuration") iConf.Load -1 ' CDO Source Defaults Set Flds = iConf.Fields With Flds .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.your servername" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "your user id" .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "your password" .Update End With -- Cheers Nigel wrote in ooglegroups.com... Hi I use Windows XP Pro, Excel 2003, Outlook Express 6.xxx. Here is what I am trying to do. I have an excel workbook, which I am trying to convert to PDF and then attach the saved PDF version of the excel sheet to an Outlook Express mail and send to different Email address, from within the Excel Workbook. I will tackle converting to PDF part later. At the moment I am trying to develop code that will create a new Email message with a PDF file as attachment in Outlook Express. I have been reading around various newsgroups. Two codes which I have come across in this forum seem close to what I want to do: Method 1. -------------- Sub MailOXpress2() Dim dest$, sujet$, texte$ Dim Rep Application.ScreenUpdating = False Rep = "C:\Documents and Settings\xxxx\Desktop\file.pdf" 'file to attach dest = " 'email address sujet = "Send a mail from XL" texte = "Send with Outlook Express from Excel" Shell "C:\Program Files\Outlook Express\msimn.exe " & _ "/mailurl:mailto:" & dest & _ "?subject=" & sujet & _ "&Body=" & texte & ", 3", vbMaximizedFocus 'send with attached workbook "Rep" SendKeys "%I" & "p" & Rep & "~" & "%s" Application.ScreenUpdating = True End Sub ------------ 2 problems he 1. While a new outlook express message correctly addressed is created, the file does not get attached. 2. I would have liked to see the message automatically go to the outbox of the Outlook Express, rather than the user clicking 'Send' to put the message. Can somebody help me address these two points? Method 2 ------------- I saw this at Ron's site. I don't know if I did the customization of the code properly... Sub CDO_Send_ActiveSheet() Dim iMsg As Object Dim iConf As Object ' Dim Flds As Variant Application.ScreenUpdating = False 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/sendusing") = 2 ' .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "Fill in your SMTP server here" ' .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 ' .Update ' End With With iMsg Set .Configuration = iConf .to = " .CC = "" .BCC = "" .From = """Me"" " .Subject = "This is a test" .TextBody = "Hi there" .AddAttachment "C:\Documents and Settings\xxxx\Desktop\file.pdf" .Send End With Set iMsg = Nothing Set iConf = Nothing Application.ScreenUpdating = True End Sub -------- This throws an error at the last step (.Send) saying SMPT server not found etc.. I am connected to internet via ADSL at the time of trying it. Can someone please help me? |
Send an email attachment (pdf file) from Excel 2003 using Outlook Express
Thanks Nigel...
That has sorted out the problem.. I can now use the SMTP server that use authentication... On Nov 16, 3:10 pm, wrote: Nigle Thanks for the quick reply...It worked even with the part of the code you mentioned commentated out. Still let me see I can use that to give authentication to the SMTP server... Thanks. On Nov 16, 2:51 pm, "Nigel" wrote: Ron's method does work, I suspect you are not setting the CDO configuation properly....something like Set iConf = CreateObject("CDO.Configuration") iConf.Load -1 ' CDO Source Defaults Set Flds = iConf.Fields With Flds .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.your servername" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "your user id" .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "your password" .Update End With -- Cheers Nigel wrote in ooglegroups.com... Hi I use Windows XP Pro, Excel 2003, Outlook Express 6.xxx. Here is what I am trying to do. I have an excel workbook, which I am trying to convert to PDF and then attach the saved PDF version of the excel sheet to an Outlook Express mail and send to different Email address, from within the Excel Workbook. I will tackle converting to PDF part later. At the moment I am trying to develop code that will create a new Email message with a PDF file as attachment in Outlook Express. I have been reading around various newsgroups. Two codes which I have come across in this forum seem close to what I want to do: Method 1. -------------- Sub MailOXpress2() Dim dest$, sujet$, texte$ Dim Rep Application.ScreenUpdating = False Rep = "C:\Documents and Settings\xxxx\Desktop\file.pdf" 'file to attach dest = " 'email address sujet = "Send a mail from XL" texte = "Send with Outlook Express from Excel" Shell "C:\Program Files\Outlook Express\msimn.exe " & _ "/mailurl:mailto:" & dest & _ "?subject=" & sujet & _ "&Body=" & texte & ", 3", vbMaximizedFocus 'send with attached workbook "Rep" SendKeys "%I" & "p" & Rep & "~" & "%s" Application.ScreenUpdating = True End Sub ------------ 2 problems he 1. While a new outlook express message correctly addressed is created, the file does not get attached. 2. I would have liked to see the message automatically go to the outbox of the Outlook Express, rather than the user clicking 'Send' to put the message. Can somebody help me address these two points? Method 2 ------------- I saw this at Ron's site. I don't know if I did the customization of the code properly... Sub CDO_Send_ActiveSheet() Dim iMsg As Object Dim iConf As Object ' Dim Flds As Variant Application.ScreenUpdating = False 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/sendusing") = 2 ' .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "Fill in your SMTP server here" ' .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 ' .Update ' End With With iMsg Set .Configuration = iConf .to = " .CC = "" .BCC = "" .From = """Me"" " .Subject = "This is a test" .TextBody = "Hi there" .AddAttachment "C:\Documents and Settings\xxxx\Desktop\file.pdf" .Send End With Set iMsg = Nothing Set iConf = Nothing Application.ScreenUpdating = True End Sub -------- This throws an error at the last step (.Send) saying SMPT server not found etc.. I am connected to internet via ADSL at the time of trying it. Can someone please help me? |
Send an email attachment (pdf file) from Excel 2003 using Outlook Express
Hi Steve
Shall appreciate if someone can also help why first method is failing... You can't add a attachement to the mail in your code example. Body is possible http://www.rondebruin.nl/mail/oebody.htm But CDO is the best way -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... Ok..some developments... I just got Ron's method to work. It work the way I wanted it...Thanks Ron. I don't know what exactly fixed the problem..these things were done... 1. Gave Excel permission to send mail in ZoneAlarm 2. Changed the From Email address from to my actual Email address (of my SMTP server). 3. In Outlook Express, ToolsAccountsMailPropertiesServersOutgoing Mail Server, unchecked 'My server require Authentication'. Now luckily I have two SMTP servers, one requiring authentication and one that do not. How do I give a password authentication through VBA if I were to use the SMPT server which need authentication... Shall appreciate if someone can also help why first method is failing... Thanks in advance |
All times are GMT +1. The time now is 07:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com