Send an email attachment (pdf file) from Excel 2003 using Outlook Express

[steveblack2006]

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 = "(e-mail address removed)" '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 here:

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 = "(e-mail address removed)"
.CC = ""
.BCC = ""
.From = """Me"" <[email protected]>"
.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?

 

[Nigel]

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

 

[steveblack2006]

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 (e-mail address removed) to my actual
Email address (of my SMTP server).
3. In Outlook Express, Tools>Accounts>Mail>Properties>Servers>Outgoing
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

 

[Nigel]

see my other reply.....

 

[steveblack2006]

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.

 

[steveblack2006]

Thanks Nigel...

That has sorted out the problem.. I can now use the SMTP server that
use authentication...

 

[Ron de Bruin]

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