Outlook message from Excel

Posted by Paula on May 29, 2001 1:04 PM

Is there a way to have an Outlook message automatically sent to specific individuals when certain criteria is met in an Excel spreadsheet? If it is possible, should I originate the message using an Outlook form or from Excel?

Posted by Jerid on May 29, 2001 1:27 PM

Hi Paula

Here is some code I got from a friend, I havent used it because we don't use Outlook, but maybe it can help you.

Sub Mailto()
On Error Resume Next

'Object variables
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

'Specific variables
Dim DMSubject As String
Dim DMMessage As String
Dim DMName As String
Dim DMAttach As String
Dim i As Integer

'Initialize variables
DMSubject = Range("Subject")
DMMessage = Range("Message")

i = 7

For i = 7 To 7
DMName = Cells(i, 2)
DMAttach = Cells(i, 3)
Set objOutlook = CreateObject("Outlook.Application")

'Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

'Set up Recipient name
With objOutlookMsg
Set objOutlookRecip = .Recipients.Add(DMName)
objOutlookRecip.Type = olTo

'Set up subject and Body
.Subject = DMSubject
.Body = DMMessage

'Add attachments to the message.
If Not IsMissing(DMAttach) Then
Set objOutlookAttach = .Attachments.Add(DMAttach)
End If

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
Exit Sub
End If
Next

'Send the message
.Send
End With

'Clear memory
Set objOutlookMsg = Nothing
Set objOutlook = Nothing

Next i

MsgBox "Done"

End Sub

Jerid

Posted by Paula on May 29, 2001 1:31 PM

'Initialize variables DMSubject = Range("Subject") DMMessage = Range("Message") i = 7 For i = 7 To 7 DMName = Cells(i, 2) DMAttach = Cells(i, 3) Set objOutlook = CreateObject("Outlook.Application") 'Create the message. Set objOutlookMsg = objOutlook.CreateItem(olMailItem) 'Set up Recipient name With objOutlookMsg Set objOutlookRecip = .Recipients.Add(DMName) objOutlookRecip.Type = olTo 'Set up subject and Body .Subject = DMSubject .Body = DMMessage 'Add attachments to the message. If Not IsMissing(DMAttach) Then Set objOutlookAttach = .Attachments.Add(DMAttach) End If ' Resolve each Recipient's name. For Each objOutlookRecip In .Recipients objOutlookRecip.Resolve If Not objOutlookRecip.Resolve Then Exit Sub End If Next 'Send the message .Send End With 'Clear memory Set objOutlookMsg = Nothing Set objOutlook = Nothing Next i MsgBox "Done"

Thanks. I'll give it a try.