Here are some old code fragments (which I haven't tested lately) that may
give you some idea of how to create and send an email directly from Excel. I
have stripped out some stuff, so it is incomplete, but will show you how to
declare your objects and use them to create and send an email.
HTH,
Eric
Option Explicit
'
' Global variables
'
Public olObj As Object ' This will be used to hook into the Outlook
application
'
Sub CreateMailMerge()
Dim i As Long, j As Long
Dim row1 As Long, row2 As Long
'
' Make sure Outlook is actually running.
'
On Error GoTo Not_Running
Set olObj = GetObject(, "Outlook.Application")
On Error GoTo 0
'
' Get the range of rows for which emails will be generated
' (This is stuff I had to generate a series of emails from an Excel worksheet)
'
email_Range_Form.Show
'
row1 = email_Range_Form.StartRowTextBox.Value
row2 = email_Range_Form.EndRowTextBox.Value
If (row1 <= 1) Then Exit Sub
If (row2 < row1) Then Exit Sub
'
For i = row1 To row2 Step 1
Call DBEntryNotice(i)
Next i
'
Exit Sub
'
Not_Running:
MsgBox "Outlook does not appear to be running. Please run Outlook first."
'
End Sub
'
' DBEntryNotice Macro
' (This sub takes the data from one row of my worksheet and
' creates the email message)
'
Sub DBEntryNotice(theItem As Long)
Dim i As Long, j As Long, k As Long
'
Dim olItem As Object
Dim olRecipient As Object
'
' Get the required information from the Excel worksheet
'
' (Here is where I grabbed information from each row to create
' my emails - I stripped that out)
'
' Create a new blank mail message.
'
Set olItem = olObj.CreateItem(olMailItem)
Set myRecipients = olItem.Recipients
olItem.BodyFormat = olFormatPlain
olItem.Display
olItem.Subject = "Mail Concerning (Your Subject Here)"
'
' (Here you can add one or more recipients for the email)
'
Set olRecipient = olItem.Recipients.Add("valid email address string here")
'
If Not myRecipients.ResolveAll Then ' Outlook doesn't know who this is
MsgBox "Unable to resolve an email address for row number " & theItem
Exit Sub
End If
olItem.Body = "This is some body text. " & Chr(10)
olItem.Body = olItem.Body & "Here I am adding another line of text to
the body." & Chr(10)
olItem.Body = olItem.Body & "etc, etc" & Chr(10)
'
' Set some outlook options:
' Delivery receipt, Read receipt, Priority
'
olItem.OriginatorDeliveryReportRequested = True
olItem.ReadReceiptRequested = True
olItem.Importance = olImportanceHigh
'
' I was saving the emails and then sending them manually...
'
olItem.Close olSave
'
' But you can easily just send them as you go...
'
' olItem.Send
'
Exit Sub
'
End Sub
