PDA

View Full Version : Email help request.



timesaver
10-18-2008, 10:52 AM
I email around 500 people per day and it is time consuming for me to keep hitting 'new message' 500 times and copy pasting the same text in.

it would be a great work for all such people who daily mail the same message to many people in the world.

The description is as under :

1) reads from a list of email adresses in excel (unique address in each row)

2) pastes each email adress into the recipient a new message in outlook

3) pastes the email text/content from a notepad file into the email body.

4) hits the 'send' button

and all of this be done with not so much of CPU time.

Demosthine
10-18-2008, 11:05 AM
Good Morning.

A co-worker and I have put together a fairly slick Function that enables you to send a mail message through the single function. This function takes arguments for the address, subject, body, etc.

From within Excel, you'll want to create a For ... Loop that cycles through your list of emails. You can a single cell at the top of the page (or another worksheet) that will contain your Subject and Body. As you cycle through the list of email addresses, pass it to the function, along with the Subject and Body. It will send your mail for you.


Attribute VB_Name = "basCDOSendMail"
Option Explicit

'//*************************************************************************** **********//
'// Author: Mark Stump //
'// Date: orig: 2007 //
'// NOTES: //
'// The ability to send mail automatically has resulted in Microsoft adding //
'// additional restrictions to what a programmer is permitted access to in //
'// Microsoft Outlook. Although this may seem to complicate the process for //
'// legitimate users, there is always a workaround. The solution is Collaborative //
'// Data Objects (CDO). //
'// //
'// In the attached example, the SMPT Server, Username and Password have been //
'// removed for security purpose. In order for the example to work, you must //
'// substitute your access information. //
'// //
'// *************************************************************************** *********//

Public strCurUserSMTP
Public strUserEmailPassword

Function CDOMail_SendMessage(SendTo As String, SendCC As String, SendBCC As String, _
From As String, ReplyTo As String, _
Subject As String, BodyHTML As String, BodyPlain As String)
' Declare the constants required to properly set the Configuration Fields
' for accessing the SMTP Server.
Const conSendUsing = 2
Const conSmtpServerPort = 25
Const conSmtpAuthenticate = 1

' Declare the variables to properly set the Configuration Fields and
' compose the Message. Because we are using Late Binding, these are
' abstract data types.
Dim objMessage As Object
Dim objConfiguration As Object
Dim varFields As Variant

' Create new instances for the Configuration and Message.
Set objConfiguration = CreateObject("CDO.Configuration")
Set objMessage = CreateObject("CDO.Message")

' Load the CDO Source Defaults.
objConfiguration.Load -1

' Set the Configuration Fields for accessing the SMTP Server.
Set varFields = objConfiguration.Fields
With varFields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = _
conSendUsing
' You may not use variables or constants while defining the SMTP Server. It
' must be a literal string.
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
""
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = _
conSmtpServerPort

.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = _
conSmtpAuthenticate
' You may not use variables or constants while defining the UserName. It
' must be a literal string.
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = _
""
' You may not use variables or constants while defining the Password. It
' must be a literal string.
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = _
""

.Update
End With

' Generate the Message using the arguments passed to the function and send the
' message.
With objMessage
Set .Configuration = objConfiguration

.To = SendTo
.CC = SendCC
.BCC = SendBCC
.From = From
.ReplyTo = ReplyTo

.Subject = Subject
' If the argument for HTML was not empty, we define the HTML Body for the
' message. This will override any text in the TextBody property of the
' message if the user is capable of viewing HTML Messages.
If BodyHTML <> "" Then _
.HTMLBody = BodyHTML
' If the argument for Plain Text was not empty, we define the Text Body for
' the message.
If BodyPlain <> "" Then _
.TextBody = BodyPlain

.Send
End With

' Process Garbage Collection to restore memory.
Set objMessage = Nothing
Set objConfiguration = Nothing
Application.EnableEvents = False
End Function



Happy Coding.
Scott