Sending An Email In Excel Programmatically W/O Sending An Object

[nouveauricheinvestments]

Hi,

I want to send an email in excel programmatically but I don't want to
send a workbook or worksheet. I also need to be able to specify the
subject, recipients, and message.

I'm looking for something similar to the 'docmd.sendobject' method in
an access database. Is there anything like that?

 

[Office_Novice]

Search excels Help file for Routing slip.

 

[nouveauricheinvestments]

Search excels Help file for Routing slip.

yeah I found routing slip. It sends the workbook. That isn't what I
want to do. I do not want to send an object to the recipient - just a
message, and I don't want the recipient to route it to anyone else. I
am going to send out passwords for a database application. I figured
it would be easier to reference and move from cell to cell including
the cell values in the message text because I don't know how to access
and move from record to record in a database table.

 

[Ron de Bruin]

Start with this example
http://www.rondebruin.nl/mail/folder3/smallmessage.htm

Read also the tips page (link is on the page)

 

[Office_Novice]

I Modified Rons Code a bit to be Table driven. Add Recipents in Column "A"
Subject in Column "B" And Body in Column "C". It will loop through Column
"A" And send to each Recipent.

Sub MailItem()
Dim Outlook As Object
Dim Mail As Object
Dim Body As String
Dim i As Long
Dim LastRow As Long
Dim ws As Worksheet

Set ws = ActiveWorkbook.Worksheets(1)
LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To LastRow
Set Outlook = CreateObject("Outlook.Application")
Outlook.Session.Logon
Set Mail = Outlook.CreateItem(0)

Body = ws.Range("C" & i).Value

On Error Resume Next

With Mail
.To = ws.Range("A" & i).Value
.Subject = ws.Range("B" & i).Value
.Body = Body
.send
End With
Next
On Error GoTo Morgue

ErrorOut:
Set Outlook = Nothing
Set Mail = Nothing
Exit Sub
Morgue:
MsgBox Err.Description, vbCritical
Resume ErrorOut
End Sub

Good Luck!

 

[nouveauricheinvestments]

Start with this examplehttp://www.rondebruin.nl/mail/folder3/smallmessage.htm

Read also the tips page (link is on the page)

Thank you for the help Ron. I am using the following and it is only
sending one email - everything in the email looks great - but I need
it to loop through every cell and send a new email for every cell.
What do you think I should do differently?

Sub SendPasswords()

Dim OutApp As Object
Dim OutMail As Object
Dim strBody As String
Dim MyRecipient As String
Dim MyMessage As String
Dim MySubject As String
Dim I As Integer


Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
Range("B2").Select
I = ActiveCell.Row

On Error Resume Next
Do Until I = 7
MyRecipient = "Robin Tanner"
MySubject = "Ticketing System and Password Release"
MyMessage = "Your username is: " & ActiveCell.Text & vbCrLf &
vbCrLf & ActiveCell.Offset(0, 2).Text
With OutMail
.To = MyRecipient
.CC = ""
.BCC = ""
.Subject = MySubject
.Body = MyMessage
.Send
End With
DoEvents
ActiveCell.Offset(1, 0).Select
I = ActiveCell.Row
Debug.Print I
Loop



End Sub

 

[Ron de Bruin]

There are a lot of examples on my site
http://www.rondebruin.nl/sendmail.htm

Check it out

 

[nouveauricheinvestments]

There are a lot of examples on my sitehttp://www.rondebruin.nl/sendmail.htm

Check it out

Do you know anything about what references I will need to have
enabled? I think I need to enable some different references...

 

[Ron de Bruin]

The code on my site not need a reference to Outlook