How to use MS outlook in an IDEAScript
Forums
I recently gave an IDEAScripting course and one of the participants had a need to be able to send emails based on a IDEA database using IDEAScript. So I searched around the net and came up with the following that has worked on my home and work computers. I have read that in some instances outlook might be set-up to reject requests from other software to send emails, if that is the case then this script would be blocked. So in this example, I have five fields in an IDEA database, one of them is the email address and the others are information to insert into the message. The following table is an example of what I used with the script below:
EMAIL_ADDRESS | FIRST_NAME | LAST_NAME | AMOUNT | TEXT |
fakeemailaddress1 @ fake.com | Jane | Doe | 100 | text1 |
fakeemailaddress2 @fake.com | John | Doe | 200 | text2 |
fakeemailaddress3 @f ake.com | John | Smith | 300 | text3 |
Sub Main
Dim OutApp As Object
Dim OutMail As Object
Dim db As database
Dim rs As Object
Dim rec As Object
Dim sSubject As String
Dim sMessage As String
Dim i As Long
Dim count As Long
Dim CrLf As String
CrLf = Chr(10) & Chr(13)
Set db = Client.OpenDatabase("outlook-Sheet1.IMD")
Set rs = db.RecordSet
count = rs.Count
rs.ToFirst
For i = 1 To count
rs.next
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set rec = rs.ActiveRecord
sSubject = "This email is for " & rs.ActiveRecord.GetCharValue("FIRST_NAME") & " " & rs.ActiveRecord.GetCharValue("LAST_NAME")
sMessage = "Dear " & rs.ActiveRecord.GetCharValue("FIRST_NAME") & CrLf & CrLf
sMessage = sMessage & "This is the body of the email, this is the amount " & rs.ActiveRecord.GetNumValue("AMOUNT")
sMessage = sMessage & " and this is the text " & rs.ActiveRecord.GetCharValue("TEXT")
On Error Resume Next
' Change the mail address and subject in the macro before you run it.
'MsgBox rs.ActiveRecord.GetCharValue("EMAIL_ADDRESS")
OutMail.To = rs.ActiveRecord.GetCharValue("EMAIL_ADDRESS")
OutMail.CC = ""
OutMail.BCC = ""
OutMail.Subject = sSubject
OutMail.Body = sMessage
' You can add other files by uncommenting the following line.
'.Attachments.Add ("C:\test.txt")
' In place of the following statement, you can use ".Display" to
' display the mail.
OutMail.Display
OutMail.Send
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Next i
Set rec = Nothing
Set rs = Nothing
Set db = Nothing
End Sub
That is great, thanks for
In reply to Hello Brian, by Humphrey