eMail attachments through Access VBA

Example using CDO

Public Sub Email270DayHolds()
Dim Db As DAO.Database
Dim ThsDay As String
Dim ThisDay As String
Dim ThisYY As String
Dim ThisMM As String
Dim ThisDD As String
Dim strTo As String
Dim strCC As String
Dim strBCC As String
Dim strFrom As String
Dim strTitle As String
Dim rs As Recordset
Dim txtAttach1 As String
Dim txtAttach2 As String
Dim strSubj As String
Dim strBody As String
Dim strNoAddr As String
Dim strSupplier As String
Dim strDate As String
Dim cdoMessage As Object
Dim objCDOMail As Object
Dim strschema As String
'On Error GoTo ErrorHandler ' Enable error-handling routine.
 
 
 
'===================================================================================================
Set Db = CurrentDb
          ThisDay = Date$
          ThsDay = Right([ThisDay], 4) & Left([ThisDay], 2) & Mid([ThisDay], 4, 2)
          strDate = ThsDay
          
          Set rs = Db.OpenRecordset("qrsEmailWithSubjAndBody")
'===================================================================================================
        
          rs.MoveFirst
          Do While Not rs.EOF
            
            strTo = rs!EmailAddr
             sTo = strTo
            strFrom = rs!myemailaddr
            
            'End If
            strSubj = rs!SubjectLine
            strBody = rs!BodyContents
                                  txtAttach2 = "C:\Acct\IC_Data\270Days\HoldsGreaterThan270DaysAtEndOfMonthSummary_" & strDate & ".xls"
            txtAttach1 = "C:\Acct\IC_Data\270Days\HoldsGreaterThan270DaysAtEndOfMonthDetails_" & strDate & ".xls"
                  '---------------------------------------------------------------------------------
             sPathFile = txtAttach1
          ' Create the message.
          
                      
                  '===================================================================================================
                Set cdoMessage = CreateObject("CDO.Message")
                Set objCDOMail = CreateObject("CDO.Configuration")
                strschema = "http://schemas.microsoft.com/cdo/configuration/"
                objCDOMail.Load -1 ' CDO Source Default
                'If you have illegal or wrong smtp address here it will run for 30-60
                'seconds and finally give transport error
                With objCDOMail.Fields
                    .Item(strschema & "sendusing") = 2 ' cdoSendUsingPort
                    .Item(strschema & "smtpserver") = "mymailhub.com" ' "Your SMTP server address here"
                    .Item(strschema & "smtpserverport") = 25 'specify port number
                    .Item(strschema & "smtpconnectiontimeout") = 120
                    .Update
                End With
 
                With cdoMessage
                    Set .Configuration = objCDOMail
                        .To = strTo
                        .FROM = strFrom
                        .CC = "me@mail.com"
                        '.Bcc = "me@mail.com"
                        .Subject = strSubj
                        .TextBody = strBody
                        .AddAttachment txtAttach1
                        .AddAttachment txtAttach2
                        .send
                End With
                 Db.Execute ("qriEmailLog")
                 sTo = ""
                
                strTo = ""
                strCC = ""
                strBCC = ""
          rs.MoveNext
          Loop
Set cdoMessage = Nothing
Set objCDOMail = Nothing
Exit Sub ' Exit to avoid handler.
ErrorHandler: ' Error-handling routine.
Debug.Print Err.Number & "-" & Err.Description
Set cdoMessage = Nothing
Set objCDOMail = Nothing
 
End Sub

Select allOpen in new window

Hi jmoss,

Thanks for the quick response.

I did see a reference to CDO Mail on experts... I have never heard of this, though. Any good links that can throw light on the reliability and an overview of the same?

Regards,
Rajesh

Hello kavirajesh,

CDO is an accepted way to do it.  I do not use that method myself, but do not take that as a criticism.

If you use Outlook, and would prefer to automate Outlook to do this, the snippet below illustrates how.
Be aware, though, that automating Outlook will trigger security warnings that may annoy you.  To deal
with the warnings, you can use:

1) Express ClickYes
http://www.contextmagic.com/express-clickyes/
There is a free version and a Pro version for ~$30 with more features.  I've used the pro version.
It works as advertised.  I'm not using it currently.  I'm using the MAPILab product instead.

2) MAPILab Advanced Outlook Security
http://www.mapilab.com/outlook/security/
A free COM add-in for Outlook.  I am using it now and I like it

3) Redemption
http://www.dimastr.com/redemption/
A DLL you can use in your code to disable the warnings.  Free download, but you must pay a
license to distribute.  I've used it and like it.

Regards,

Patrick

Dim olApp As Object
Dim olMsg As Object 
Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(0)
With olMsg
    .To = "X@z.com"
    .CC = "y@z.com"
    .BCC = "z@z.com"
    .Subject = "subject"
    .Body = "body"
    .Attachments.Add "c:\folder\subfolder\foo.xls"
    .Send
End With 
Set olMsg = Nothing
Set olApp = Nothing

Select allOpen in new window

The reason that I use CDO is that it bypasses the security problems of Outlook; the problem of your sent message box getting full from all the messages, ability to use another sending email address with ease. If you have a mail server then you most likely have an SMTP server which CDO uses.

Thanks Patrick. I did read an earlier post from you which spoke of the security concerns and gave the 3 options... but I did not see the Outlook code there..

I am going to put both of these together and see if it works for me.

Couple of small questions on this
1) Will this leave a copy of the message in Sent Items?
2) Will the recipient be able to do a "Reply-To" on this message if required?
3) Is there a limitation on the attachment size?
4) There seems to be no exception handling of any sort happening in the code snippet you have given... not necessary? Haven't done eMail automation before... hence all these q's!

Thanks for the response. It is 12AM where I come from... I dont know how much longer I am going to be on. Will get back on this asap.

Regards,
Rajesh

Mail via CDO has been around for quite awhile and is very reliable. My apps send around 15,000 to 20,000 emails a week using this method and have for years without a hitch.

Hi jmoss,

I understand that you have added in db's and recordsets and stuff to get the data from... but it does make the code sound awfully long!!

Would it be possible for you to assume that I will figure out how to get the raw data for each of the "To", "Cc", "Subject" etc. and just give me only the CDO part of the code?

I took one look at it and said, "Hey! 75 lines of code, with YY, MM and DD does not seem necessary for sending out a mail!!" :-)

Also, as requested in my earlier post, can you point me in the direction of some informative, sound link on CDO?

This is quite urgent (which explains the 500 points :-)

Regards,
Rajesh

Patrick,

Is the MAPI add-in stable with Outlook 2007?

One thing missing in your solution was the From address? Right now, I dont have access to an SMTP or I would have tested it out. Can I use any "From" address and send out the message? I have multiple accounts configured in my Outlook. Which of those will get picked? Little confused!

Regards,
Rajesh

Hi jmoss,

What would the references be for CDO?

Regards,
Rajesh

You may be better off using Patrick's method; I'd stay away from click yes though, because it's way too slow.

kavirajesh,

There is no reference, you're calling all you need in the vba

Jim

jmoss,.

I was thinking on the lines of giving a shot on both CDO and what Patrick's said. Why would you think Patrick's is better?

And Google did throw up tons of links on CDO :-) which is why I wanted to know if there were any sites which had good,solid, current info. Skip that request though. I will figure it out.

Regards.
Rajesh

PS: And I figured for References, I will need to add Microsoft CDO for Windows 2000.

Jim,

I put in the data that is relevant to me, and in the .send, I am getting this error message

"The server rejected one or more recipient addresses.The server response was 550 to Relay."

I am assuming the line
.Item(cdoSendUsingMethod) = cdoSendUsingPort
must be left as is? I am assuming cdoSendUsingPort is some constant.

Regards,
Rajesh

Jim,

My mistake, I guess. It worked now. I was using the outgoing SMTP mail server that my mail service provider had given me... it didnt work... I tried my Incoming Mail Server, and it worked. :-)

I will try the attachment bit, and let you know.

I am curious to understand why you said Patrick's solution was better? I am sure his solution will work too. Given the amount of time you have spent on this,and the fact that it works, I would definintely want to accept your solution. That said, if Pat's is better, then I would pick that (if it works painlessly) and accept multiple solutions.

Regards,
Rajesh

I didn't say better, but maybe easier to understand for you to get up and running quicker. My method has no references, no need for security workarounds, is more versatile, but maybe a bit more challenging. And if you're under the gun to get working maybe it's the way to go for you. I would never use Outlook but to each his own.

Regards,

Jim

I couldn't give you any good links for CDO because once I got it running I've had no problem. I've been using CDO for about eight years so any links I had would be lost by now. Microsoft would be a good place to search though.

The 550 may be thrown due to bad email address; using port is 2 for relay use 1. I use 2

Jim,

mmm...  I have got attachments pretty much working with CDO and mails going where I want them to. So I am kinda assuming I have the complexity part worked out for now.

The key questions remaining on CDO are
1) What kind of attachment sizes are permitted?
2) Will a Reply-To be possible for the recipient?
3) Does CDO have an option that helps in zipping up attachments?
4) Also, a one-liner eMail came up with 5KB, Why is that?

I am not stuck to Outlook like I mentioned in my original post... since my company is a Microsoft Gold Certified Partner, it just seems to be disloyal not to consider Outlook :-)

Regards,
Rajesh

PS: I took out the Microsoft CDO For Windows 2000 reference and it bombs at the cdoSendUsingPort line... I put it back on, and it works like a charm.
PPS: I really appreciate the speed with which you are getting back to me... looks more like a phone call than a thread to me :-)

1. I routinely send files in excess of 8mb zipped
2. Reply to affirmative. Your sent from should be set up to receive mail.
3. I have a module that zips using 7zip
4. I have no idea

CDO is part of Microsoft OS; I just don't like using it for all my aforementioned reasons, none the least of which is security and the inability of Outlook not being able to use another from address. I love Microsoft products I make a nice living with their products.

1) Great!
2) Cool.
3) can you shed a little more light please? I have 7-zip... or winzip... or Filzip...
4) The question was with specific reference to the test mails I sent using CDO.. it wasnt a generic question :-) Mail size is of particular concern to me, since the recipients may have restricted bandwidths...

Regards,
Rajesh

I really don't understand, email came up with 5k... is 5k larger than email sent from Outlook? I never really cared about small message only file sizes, I would have thought that it would be equivalent to Outlook.

My mailing are generated in steps. Build Excel files, zip excel files, mail zip file and log the mailing.

Jim,

 I never have checked email sizes of small mails either which is why I am surprised that Outlook also throws about the same size for a one-liner eMail. Nothing to do with CDO I guess.

The question of zipping still remains... I will do it in a separate thread.... hope you will be able to share some of that code with me.

I think I am going to stop this thread right about here! Thanks for all the help, Jim!!

Warm Regards,
Rajesh

You're welcome Rajesh.

I'm pretty sure that I have the zip module here at home; it does use 7zip though.

Jim

7-zip is just fine... if you find the module, do remember to post it!

Regards,
Rajesh

I already looked and thought that it was here but I don't have that project here on this system.

Rajesh,

I'm glad you got the help you needed.  The CDO approach is time tested, and people who use it swear by
it.  I haven't used it, but that's just force of habit than anything else.

I have not tested the MaoiLab add-in with Outlook 2007.  It works well with Outlook 2003, and I also do some
development work with Redemption.

Cheers,

Patrick

Thanks for the support, Patrick. I have got the CDO thing working just fine... the entire requirement is done.... I am sure I would have taken a day or two longer if the kind of support that experts-exchange offers, wasnt around!

Warm Regards,
Rajesh