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
Dear Brian,
Dear Brian,
Thank you for the sample code.
How do I go about this if I intend to embed images in my email.
At the moment, I reference the images on a network folder and when users receive the email, they have to wait for the images to download. See code snippet below:
olMailItm.HTMLBody = "<table align=""center""><tr><td><img src=""\\networkpath\" + staffNoField +"""></img></td><td><img src=""\\networkPath\Banner""></img></td></tr>"
Is there a way I can embed these images so as to avoid the need for it to download on client's systems?
Hi oseroke,
Hi oseroke,
I found this code for embedding an image. You first have to attach the image and then you imbed it. This should get you going to modify your code.
Const olByValue = 1 'used for attaching the image, place at beginning of your code
OutMail.Attachments.Add "C:\Users\elementb\Pictures\MyPicture.jpg", olByValue, 0
OutMail.HTMLBody = "<br><B>Embedded Image:</B><br><img src='cid:MyPicture.jpg' width='500' height='200'><br>" 'insert into your html body
Thanks
Brian
Hi Simon,
Hi Simon,
I was doing a google search and this seems to be a common problem if you edit the text. It seems that the signature is suppose to be stored here:
"C:\Users\"& Environ("username")&"\AppData\Roaming\Microsoft\Signatures"
I don't have this on my computer so can you check to see if you have it on yours? So if the file is there it is just a matter of reading the contents and attaching it to the body manually.
Hi Humphrey
Not sure which version of IDEA you are using. If you have 11.2 then there is a new email function that replaces the previous one. I would suggest looking in the language browser for Client.SendEmail if you have a previous version, you have the latest version then check out Client.Email to get the syntax on how to set it up.
Hello Brian,
Hello Brian,
Thank you for your response.
I am using IDEA version 10. Am glad to inform you that I modified the macro you shared for sending mail and it worked. What I basically did was to do away with the database and hardcode the recipient's email address and the subject. See the macro below.
--
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 OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
sSubject = "Mail from IDEA"
sMessage = "Dear Sir/Madam," & CrLf & CrLf
On Error Resume Next
OutMail.To = "somebody@domain.com"
OutMail.CC = ""
OutMail.BCC = ""
OutMail.Subject = sSubject
OutMail.Body = sMessage
OutMail.Display
'OutMail.Send
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Very useful information about
Very useful information about sending mail through IDEA Script.