Visual Basic and email automation 2

[curtin100]

I am trying to determine a quick way to create an email message, attach a file and then send out the multiple emails. I would like to tie the code to a button that would just kick off the process, the file attachement is the easy part, but any ideas on how to begin the multiple mail message generation?

-Greg

 

[VBAjedi]

Depends on the app you are starting from. I would probably do this from Excel, throwing together a table of email addresses with associated first/last names, companies, etc.

Then just loop through the rows, using the names, companies, and whatever else to personalize the email text, and sending the email to the address listed.

I often use this chunk of code for email generation. It would be easy to nest it in a loop to send an email for each row in an Excel range:

Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
With Itm
.Subject = ESubject
.To = SendTo
.Body = Ebody
'.Attachments.Add (NewFileName) ‘ Must be complete path
‘.Display ‘ This property is used when you want the user to see email and manually send. Then comment out rest of code except “End With” statement
.send
End With
Set App = Nothing
Set Itm = Nothing

Let me know if this helps!

VBAjedi

 

[MasterLu]

Hi - i'm very new at this and i'm trying to do something similar. I have an excel file of all my contacts and their email addresses and i'd like to send a standard email to each person but attach unique files to each person's email. i thought there could be a way to add a column with a link to each person's file, but there's probably a better way. I send these reports to their respective recipients once a month and would love to be able to automate the process. Also, are there good reference books/online resources you can recommend for source code/real examples? you say "It would be easy to nest it in a loop to send an email for each row in an Excel range" but i'd really need to see it.

 

[VBAjedi]

Doesn't sound too difficult. Let's give it a go. I'll pretend that the first row of actual data on your spreadsheet is row 3, and that your columns are laid out as follows:

A: "First Name"
B: "Last Name"
C: "Title (Mr/Mrs/etc)"
D: "Email Address"
E: "Subject"
F: "Body Text"
G: "Complete Attacment Path"

You could add to those as you anticipate you might have need to (TIP: much easier to add columns now than when you've written a bunch of code!). Anyway, here's some code:

Dim Sht as Worksheet
Dim FirstRow as integer, LastRow as Integer
Dim App, Itm
Dim SendTo as String, Esubject as string, Ebody as string
Dim NewFileName as String

Set Sht = Worksheets("Sheet1")
Set App = CreateObject("Outlook.Application")
FirstRow = 3
LastRow = 100 ' You could insert code here to find last row
For x = FirstRow to LastRow
SendTo = Sht.Range("D" & x).Value
If Len(SendTo) > 0 Then ' Make sure there is data in this row
Esubject = Sht.Range("E" & x).Value
Ebody = Sht.Range("F" & x).Value
NewFileName = Sht.Range("G" & x).Value

Set Itm = App.CreateItem(0)
With Itm
.Subject = ESubject
.To = SendTo
.CC = "YourEmail@YourCompany" ' If you want a confirmation copy
.Body = Ebody
If Len(NewFileName) > 0 Then .Attachments.Add (NewFileName) ‘ Must be complete path
‘.Display ‘ This property is used when you want the user to see email and manually send. Then comment out rest of code except “End With” statement
.send
End With
End If
Set App = Nothing
Next x

Set Itm = Nothing
End Sub

Put this code in a button or in a module and call it once a month or whatever. I just tapped this code out and don't have time to really test it right now - if you get to it before I do let me know if you run into any bugs.

Keep me posted!

VBAjedi

 

[MasterLu]

To VBAJedi
I haven't had a chance to try it out yet, but i wanted to say thanks for your help first!!! I'll let u know how it goes...

 

[krinid]

Related questions:
1) Does this work for Outlook only, or Outlook Express as well?
2) Is there a way to send email (from VBA) without using another application (in this case, Outlook)? (for the case when Outlook isn't installed)

 

[VBAjedi]

I have only used this method through Outlook, so I don't know about Outlook Express.

As far as working through Excel alone, I'm not aware of a way to send email without interfacing with an email client. I suppose that if you had a different email program, and it had a VBA library you could add in, you could probably work something up. You can call the default "Send file as attachment" dialog with:

Application.Dialogs(xlDialogSendMail).Show

but I think that dialog is still subject to the following quote from Excel's help files:

To send or route workbooks through e-mail, you need one of the following:

·Microsoft Exchange (or other mail system compatible with the Messaging Application Programming Interface [MAPI]).
·Lotus cc:Mail (or other mail system compatible with Vendor Independent Messaging [VIM]).


Hope that helps!


VBAjedi

 

[Tiglet]

Hi,

Here's a macro to send thru any Mapi compliant email system.
It's currently set for Novell Groupwise (hence the sProfile line of code - this may be superfluous for some email programs)

Tiglet

Sub MapiSendMail()
Dim objSession As Object
Dim objMessage As Object
Dim objRecipient As Object
Dim sProfile As String
Dim sSubjPrmpt As String
Dim sTextPrmpt As String
Dim sEmailPrmpt As String
Dim sMsgTitle As String

sProfile = "Novell Default Settings"

sEmailPrmpt = "Enter valid Email Name of message recipient:"
sSubjPrmpt = "Enter the subject line for this message:"
sTextPrmpt = "Enter the text for this message:"
sMsgTitle = "Mapi Macro"
' Create the Session Object.
Set objSession = CreateObject("MAPI.session&quot

objSession.Logon profileName:=sProfile
' Add a new message object to the OutBox.
Set objMessage = objSession.Outbox.Messages.Add
' Set the properties of the message object.
objMessage.Subject = InputBox(sSubjPrmpt, sMsgTitle)
objMessage.Text = InputBox(sTextPrmpt, sMsgTitle)
' Add a recipient object to the objMessage.Recipients collection.
objMessage.Attachments.Add "c:\path name"
Set objRecipient = objMessage.Recipients.Add
' Set the properties of the recipient object.
objRecipient.Name = InputBox(sEmailPrmpt, sMsgTitle)
objRecipient.Resolve
objMessage.send showdialog:=False
MsgBox "Message sent successfully!"
' Log off using the session object.
objSession.Logoff
End Sub

 

[daminator]

A very Thanks to U for replying me Tiglet
I have tried this out but
the problem is with password.

i should be able to set the
profile and password of a specific user through
vb.

so do u have an solution for that.

mohit.

 

[Tiglet]

daminator,

I don't have a specific solution but I would guess something along the lines of:-

Dim EmailPassword As String


EmailPassword = "Enter Password here"

What email system are you using?

Tiglet

 

[daminator]

i want to build an appn in VB6.0 that will
mail from Novell Groupwise 5.5 directly without asking for any username or password to the user. it will take it directly through vb.

for that i have to connect to Novell Groupwise Mail Server.

Thats The problem.
Not able to connect to the mail server.

 

[Tiglet]

daminator

Check out this thread, it gives code specifically for Groupwise emails from vb and somebody else has said it works for them.

thread707-579843


Tiglet

[green]Duct tape is like the force; it has a light side & a dark side, and it holds the universe together. [/green]

 

[MasterLu]

vbajedi

Thanks for the script. it works!!! you're the best!!! at first i was getting this error msg: Object variable or With block variable not set (Error 91) that would pop up after the
"Set Itm = App.CreateItem(0)"
line but it works now after i moved the
"Set App = CreateObject("Outlook.Application&quot"
in front of it. but now my problem is that Outlook prompts me to click "yes" to allow another program to perform these actions in Outlook for each email i'm sending. i'm not sure how to get around this - but i'm sure there's a simple way. Anyone's advice will be much appreciated!!!

 

[TSO456]

MasterLu

Would you be able to please email me the excel example that you got to work to daylight456@hotmail.com

I would really appreciate it

Thanks
jill