**RESOLVED**Code to send emails with attachements

[gtg689a]

Hello, I found this webiste while doing searches on the web and it looks like you guys know everything there is to know about VB. I on the other hand have been working for my company for one month and have a small understanding of VB. Basically, I can sort of read it but can't write it.
This is what I am trying to do:
Every month I have to send out 50 emails to managers. The emails all have a common format, with only a couple of words being changed in any of them. I also have to attach a different file to each one of them. The file locations and names never change(they get updated automatically.) So it would be nice to have a macro that would open a new email, insert the common text with the personalized parts as well, and then pull the file from its location and send it.

It takes forever to go through each one and change the couple of words around and attach the file. I am thinking there has got to be an easier way. Can someone point me to some code, I can barely navigate through these forums.

I know i sound like and idot, but I would really appreciate someones help!

Thanks! and have a great day!

[Hack]

Welcome to the forums.

So it would be nice to have a macro

Are you planning on doing this using VB6 or Outlook VBA?

[gtg689a]

HAHA, Ok, now I feel like a real idiot. I thought they were the same thing.

When I clicked on "About" in the Microsoft Visual Basic Editor in Excel and Outlook, it said Visual Basic Editor 6.3.

[Hack]

No, you are not an idiot. It is a pretty common misconception. VBA is actually a subset of VB that is used in all Office products.

The folks here in Office Development are the ones that can help you with VBA macro questions.

Moved to Office Development, and once again, Welcome aboard!

[gtg689a]

Thank you so much, I love this forum already!!

[billhuard]

take a look a this page from microsoft, sending mail using cdo with VB
http://support.microsoft.com/?kbid=161833

[gtg689a]

That is very helpful, but can I put that code directly in to the visual basic editor in Excel?

I am not really sure what the CDO library is either.

Thanks for your help!

[salvelinus]

The example billhuard linked to assumes you're using Outlook, so if you are, great. You'd also have to install the cdo file mentioned in the article, and the code doesn't mention attachments, so you'd need to add that.
If the emails are different (message, attachment) they'd have to be sent separately, although for identical messages you could just add email addresses. You might be able to set it up in a loop if there's something to match messages & attachments with names, i.e. if this info is in tables in Access.

I've never used this method, instead using either the .SendObject command in Access or creating an Outlook object in Access or Excel and using that.
Here's a similar recent thread.

[billhuard]

here is a link that send e-mail with cdo implented directly in excel using CDO
all kind of e-mail, small, big, with attachement, etc.. and no outlook needed
have a look at it, should anwser all your questions

http://www.rondebruin.nl/cdo.htm

[gtg689a]

I think I should clarify what I want to be able to do.

I will create an excel sheet that has columns for Name, Email Address, Subject Line, Attachment Location(on our network), and the message body.

I want a macro to be in the workbook that will open Outlook and send each of the emails(I want to be able to send it through Outlook so that I have a record of the email).

I don't know if I can get "CDO" on my computer because I do not have the Office install CD.

I appreciate all of your adivce, I'm just a little confused.

[DKenny]

Gtg
Have a look at the attached workbook. It has a procedure that sends a series of emails based on the contents of a worksheet

[billhuard]

gtg689a, You need absolutely NOTHING to use CDO.

First 2 lines of my link

Read this!!!

This code will not work in Win 98 and ME.
You must be connected to the internet when you run a example. It is possible that you get a Send error when you use one of the examples.
AFAIK : This will happen if you haven't setup an account in Outlook Express.
In that case the system doesn't know the name of your SMTP server.
If this happens you can use the commented blue lines in each example.
Don't forget to fill in the SMTP server name in each code sample where
it says "Fill in your SMTP server here"

All you need is to be connected to the internet.

10 lines further

What is CDO doing

The example code is using CDOSYS (CDO for Windows 2000).
It does not depend on MAPI or CDO and hence is dialog free
and does not use your mailbox to send email.
<You can send mail without a mail program or mail account>

Briefly to explain, this code builds the message and drops it
in the pickup directory, and SMTP service running on the machine
picks it up and send it out to the internet.


Why using CDO code instead of Outlook automation or Application.SendMail in VBA.

1: It doesn't matter what Mail program you are using (It only use the SMTP server).
2: It doesn't matter what Office version you are using (97…2003)
3: You can send a sheet in the body of the mail (some mail programs can’t do this)
4: You can send any file you like (Word, PDF, PowerPoint, TXT files,….)
5: No Outlook Security warning anymore, really great if you are sending a
lot of mail in a loop.

mid page

Sub CDO_Send_Workbook()
    Dim iMsg As Object
    Dim iConf As Object
    Dim wb As Workbook
    Dim WBname As String
    '    Dim Flds As Variant
 
    Application.ScreenUpdating = False
    Set wb = ActiveWorkbook 
 
   
 
    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 = "[email protected]"
        .CC = ""
        .BCC = ""
        .From = """Ron"" <[email protected]>"
        .Subject = "This is a test"
        .TextBody = "This is the body text"
        .AddAttachment "C:/" & WBname
        .Send
    End With
 
 End Sub

if you do not have Outlook intalled on your PC, just uncomment the code commented.

Then you just need to put this code in a loop and put some code to change the text and define some variable to change recepient and textbody

[gtg689a]

Declan,

Wow, that is awesome man, I am going to work with it for a while and adjust it to my needs but that is pretty much what I needed.

One question though:

In the code you can set the subject line, body, etc...but how do you attach a file.
is it:

.attach = c:\desktop\file1.xls


???

Thanks!

[DKenny]

Not quite, here's a snippet from my original post with a line added showing how to Add an Attachment.

With OutMail
    .To = EM_MAIL
    .Subject = MySubject
    .Body = MyBody
    .CC = PM_MAIL
    '.Send 'Use this to send
    .Display 'used this when debugging
    
    .Attachments.Add "c:\desktop\file1.xls"
 
End With

[gtg689a]

Well this issue is officially resolved!

Thank you all so much for helping me out. I am really impressed. This was my first visit to this board, and I am definitly not a VB whiz or anything. You were all so helpful and I really appreciate it!

Thanks Again!

[Vismix]

here is a link that send e-mail with cdo implented directly in excel using CDO
all kind of e-mail, small, big, with attachement, etc.. and no outlook needed
have a look at it, should anwser all your questions

http://www.rondebruin.nl/cdo.htm

Just what I needed

[Vismix]

Now I would like to recieve a message when a mail bounces, how would I do that? Any ideas?

[Vismix]

Never mind

[Vismix]

Uhm, does anyone know how to insert an URL in the mail's body? Like an online newsletter that will show up in the mail client the way it is online.

Is that understandable?