Which references am I missing, to call Outlook from Excel VBA?

[ker_01]

Several years ago, on a different PC and under a different set of IS
policies, I used the following code as part of a larger process to generate
automated emails from Excel and put images of an Excel range in each email
(customized by recipient).

I'm trying to adapt that code to do the same type of thing on a new project,
but with a totally different PC, and I'm having trouble getting it to run.
The current setup is Outlook 2003 on XP, which is the same as the other setup
where this code did work.

The error message I'm getting (see highlighted line below) is "compile
error: user defined type not defined" which suggests that there is no valid
reference registered.

I'd appreciate any suggestions- I'm stuck!
Thank you,
Keith

The checked references include:
Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Outlook 11.0 Object Library
Microsoft CDO for Windows 2000 library (which is strange, since I'm using XP?)

The top part of my code:

Function EmbeddedHTMLGraphicDemo(SendToName As String, SendFileCount As
Integer)
' Outlook objects
Dim objApp As Outlook.Application
Dim l_Msg As MailItem
Dim colAttach As Outlook.Attachments
Dim l_Attach As Outlook.Attachment
Dim oSession As MAPI.Session '<-- debug highlights here
' CDO objects
Dim oMsg As MAPI.Message
Dim oAttachs As MAPI.Attachments
Dim oAttach As MAPI.Attachment
Dim colFields As MAPI.Fields
Dim oField As MAPI.Field

'etc

 

[ryguy7272]

Try 'Microsoft Scripting Runtime'

HTH,
Ryan---

 

[ker_01]

I've added MSR, but am still getting the same error. It still highlights this
row:
Any additional ideas? I've been assuming that it was an issue with checked
references, but could it be anything else?

Thanks!
Keith

 

[ker_01]

ok, I got a copy of the source workbook that contained code I knew worked a
few years ago, and opened it to see what missing references showed up. In my
current workbook I had added MS CDO for Win 2000, and it didn't show any
missing libraries. However, when I opened the original workbook and checked
the references, it showed MS CDO 1.21 as missing. So, I found this page
(http://www.ssw.com.au/ssw/kb/KB.aspx?KBID=Q757100) that gave instructions on
how to add CDO1.21 back in, and now I'm past the original point where it was
stuck. Now, however, it stops at:
with a 424 runtime error, object required.

I'd appreciate any ideas you might have on what might be the problem!

note: code mishmashed together from folks in this group and the outlook
programming group several years ago- I don't take any credit for the parts
that work, just the parts that don't.

Thanks,
Keith


Function EmbeddedHTMLGraphicDemo(SendToName As String, SendFileCount As
Integer)
' Outlook objects
Dim objApp As Outlook.Application
'Dim l_Msg As MailItem
Dim l_Msg As Outlook.MailItem
Dim colAttach As Outlook.Attachments
Dim l_Attach As Outlook.Attachment

Set OutMail = OutApp.CreateItem(olMailItem) '<-- new error point

' CDO objects
Dim oSession As MAPI.Session
Dim oMsg As MAPI.Message
Dim oAttachs As MAPI.Attachments
Dim oAttach As MAPI.Attachment
Dim colFields As MAPI.Fields
Dim oField As MAPI.Field

Dim strEntryID As String

' create new Outlook MailItem
Set objApp = CreateObject("Outlook.Application")
Set l_Msg = objApp.CreateItem(olMailItem)
' add graphic as attachment to Outlook message
' change path to graphic as needed
Set colAttach = l_Msg.Attachments

FName = "c:\" & SendToName
FExt = ".gif"

For n = 1 To SendFileCount
Set l_Attach = colAttach.Add(FName & CStr(n) & FExt) '& "<br> </br>" &
"test text" & "<br> </br>"
Next

l_Msg.Close olSave
strEntryID = l_Msg.EntryID
Set l_Msg = Nothing
' *** POSITION CRITICAL *** you must dereference the
' attachment objects before changing their properties
' via CDO
Set colAttach = Nothing
Set l_Attach = Nothing

' initialize CDO session
On Error Resume Next
Set oSession = CreateObject("MAPI.Session")
oSession.Logon "", "", False, False

' get the message created earlier
Set oMsg = oSession.GetMessage(strEntryID)
' set properties of the attached graphic that make
' it embedded and give it an ID for use in an <IMG> tag
Set oAttachs = oMsg.Attachments
For n = 1 To SendFileCount
Set oAttach = oAttachs.Item(n)
Set colFields = oAttach.Fields
Set oField = colFields.Add(CdoPR_ATTACH_MIME_TAG, "image/jpeg") '??
Set oField = colFields.Add(&H3712001E, "myident" & CStr(n))
Next
oMsg.Fields.Add "{0820060000000000C000000000000046}0x8514", 11, True
oMsg.Update

' get the Outlook MailItem again
Set l_Msg = objApp.GetNamespace("MAPI").GetItemFromID(strEntryID)
' add HTML content -- the <IMG> tag
HTMLString = ""
For n = 1 To SendFileCount
HTMLString = HTMLString & "<IMG align=baseline border=0 hspace=0
src=cid:myident" & _
CStr(n) & ">" & "<br> </br>" & _
"<p> & </p>" & _
"Please review the list of attached open orders." & _
"<br> </br> <br> </br>"
Next
l_Msg.HTMLBody = HTMLString
l_Msg.To = SendToName
l_Msg.Subject = "Daily report; Please review and reply"
l_Msg.Close (olSave)
l_Msg.Display
l_Msg.Send

' clean up objects
Set oField = Nothing
Set colFields = Nothing
Set oMsg = Nothing
oSession.Logoff
Set oSession = Nothing
Set objApp = Nothing
Set l_Msg = Nothing
End Function

 

[ker_01]

It appears that the problematic line was somehow in the wrong place, and a
duplicate. Once I deleted it, the code now works as expected. Woot!
Keith