How to use MS outlook in an IDEAScript

8 posts / 0 new
Last post
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57
How to use MS outlook in an IDEAScript

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

 

chandra.ra.poojari's picture
chandra.ra.poojari
Offline
Joined: 01/08/2016 - 03:31

Very useful information about sending mail through IDEA Script.

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Thanks Chandra and welcome to the site.

Brian

oseroke
Offline
Joined: 01/06/2016 - 09:47

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?
 
 
 
 

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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

oseroke
Offline
Joined: 01/06/2016 - 09:47

Thank you.
This worked.

Simon Humphreys
Offline
Joined: 04/25/2019 - 09:05

Hi,
 
When I type in the body of the email, it overwrites my signature.  Is there an easy way to ensure the signature remains when entering text? 
 
Thanks,
 
Simon

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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.