How to handle users with various office libraries/Outlook OLE

[Eug7]

Thanks so much Vlad for the help. In my code I left out Dim ol As OutlookHandler otherwise the code I pasted is as complete (as far as I can tell).



Command triggers:

Option Compare Database


Dim ol As OutlookHandler



Set ol = New OutlookHandler

With ol.msg
.Subject = "some topic"
.HTMLBody = "some explanation"
.To = "outgoing email"
.CC = "outgoing email CC"
.Attachments.Add "(some attachement.xlsx)"
.Display
End With
End function



Set ol = New OutlookHandler triggers:


Option Compare Database
Public WithEvents app As Outlook.Application
Public WithEvents msg As Outlook.MailItem


Private Sub Class_Initialize()

Set app = CreateObject("Outlook.Application")
Set msg = app.CreateItem(olMailItem)

End Sub

[Gicu]

You don't seem to like the code tags... Your post #4 has some totally different code then the one above, so I am confused what you tried and what exact error you are getting. And why the need for a class module for such a simple task? I used to use a WithEvents Outlook module to log the emails being sent but you don't seem to do\need that.
Here is a simple function that would allow you to do what your current code is doing without any OutlookHandler module; just add it to any standard module and call it from anywhere in your app providing the needed arguments:

Function vcSendEmail_Outlook_With_Attachment(sSubject As String, sTo As String, Optional sCC As String, Optional sBcc As String, Optional sAttachment As String, Optional sBody As String)

Dim OutApp As Object
Dim OutMail As Object
   
Set OutApp = CreateObject("Outlook.Application")
'OutApp.Session.Logon 'might need to uncomment this


Set OutMail = OutApp.CreateItem(0)
 
OutMail.To = sTo
If sCC <> "" Then OutMail.CC = sCC
If sBcc <> "" Then OutMail.BCC = sBcc
OutMail.Subject = sSubject
If sBody <> "" Then OutMail.HTMLBody = sBody
If sAttachment <> "" Then OutMail.Attachments.Add (sAttachment)


OutMail.Display  'Send | Display


Set OutMail = Nothing
Set OutApp = Nothing
End Function

Cheers,

[Eug7]

Vlad,

Thank you so much. Success on the ability to create the email in a runtime environment. Can you help more please? I have two additional issues.
1. You make reference to "WithEvents Outlook module to log the emails being sent". I do need the ability to create a log record of sent email (I do it now with current function OutlookHandler module). How do I implement the same with your solution? Need to run Post send (after the send button).

2. My second issue is related again to what I believe is a library mismatch between users machines. I create in MS Access 16 thus "Microsoft Access 16.0 Object Library" and "Microsoft Office 16.0 Access database engine Object" libraries selected. So, certain code won't run. More specifically creating a DAO recordset. Is there a way around this issue?

Thank you.

[Gicu]

Hi,
Can you post what you currently use for #1?
As for #2 I am sure that the Access libraries get reset automatically based on the installed version so there isn't anything to do. Can you show how you create the DAO recordset? Do you fully qualify it in the declaration (Dim rs as DAO.Recordset)? I can see this being a problem if you open the file in a pre Access 2010 version as I believe that is when the DAO library has been incorporated into the Office database engine object library. Is the file a mdb?
Cheers,

[Eug7]

Vlad, Issue 1, The code I was using to send was code I picked up years ago on the net. I'm not sure how "msg_Send(Cancel As Boolean)" is triggered after the send button but it is. Then I have code on "msg_Send(Cancel As Boolean)" that appends a log record.

'Old code:




Issue 2: is as below recordset which I think is causing the crash on runtime with another user.

Dim gRst As Dao.Recordset
Set gRst = CurrentDb.OpenRecordset(varWhere, dbOpenDynaset, dbReadOnly)
If gRst.RecordCount = 0 Then
gRst.Close
MsgBox "No data."
Stop
Exit Sub
Else
End If

[Eug7]

I see where my paste of old code did not appear

[Gicu]

Sorry, but I can't really help you if you don't provide the code. For the DAO recordset the code probably crashes the runtime because you have a Stop in there and in the runtime you don't have access to the VBE environment. As for the logging as I say, I can't help based on a small screen shot .

Cheers,

[Eug7]

Vlad, I don't know what to say... code is minimal,

This off a command:

Dim ol As OutlookHandler
Set ol = New OutlookHandler


With ol.msg
.Subject = "This subject"
.HTMLBody = "This Body"
.Send
End With


That triggers this class module that opens Outlook:

Public WithEvents app As Outlook.Application
Public WithEvents msg As Outlook.MailItem


Private Sub Class_Initialize()


Set app = CreateObject("Outlook.Application")
Set msg = app.CreateItem(olMailItem)



...and this runs asfter send within the same class module as a sub: I just don'r know how this gets triggered once the send in Ooutlook is hit.

"Private Sub msg_Send(Cancel As Boolean)"

[Gicu]

Could you paste the code from the msg_Send sub in here using the # code tags?

[Eug7]

I can paste it and hopefully I paste it properly but the contents is all custom code that creates log records by the specific form emailed out. The question is really what triggers "msg_Send sub" after the send button is hit.



[Private Sub msg_Send(Cancel As Boolean)

Dim sqlStmt As String
Dim rst, rstMRPLog As DAO.Recordset

Dim varLineNote As Variant
Dim intLen As Integer

gDate = Now

Select Case gInteger

Case 50 '50=Reschedule email procedure.

Call Forms("fdlgReschedule").fnAppendLog
'/*-------------------------------------------------------------------------------------------------------------------------------------------------*/
'/*-------------------------------------------------------------------------------------------------------------------------------------------------*/
Case 51 '50=Open PO email procedure.


Call Forms("fdlgOpenOrderReport").fnAppendLog
'/*-------------------------------------------------------------------------------------------------------------------------------------------------*/
'/*-------------------------------------------------------------------------------------------------------------------------------------------------*/
Case 52

Call Forms("fdlgRelease").fnAppendLog
'/*-------------------------------------------------------------------------------------------------------------------------------------------------*/
'/*-------------------------------------------------------------------------------------------------------------------------------------------------*/
Case 53

Call Form_fsubRFQ.fnAppendLog
'/*-------------------------------------------------------------------------------------------------------------------------------------------------*/
'/*-------------------------------------------------------------------------------------------------------------------------------------------------*/
Case 54
'Flux report.
' Kill "c:\users" & gOutlookUserName & "\documents" & gOutlookAttachment & ".xls"
'/*-------------------------------------------------------------------------------------------------------------------------------------------------*/
Case 55

Kill "c:\users" & gOutlookUserName & "\documents" & gOutlookAttachment & ".xls"
'/*-------------------------------------------------------------------------------------------------------------------------------------------------*/
'/*-------------------------------------------------------------------------------------------------------------------------------------------------*/
Case 56

Call Form_fsubPO.fnSendConfirm

End Select
gInteger = 0


End Sub][/CODE]

[Gicu]

You should open the VBE window and do a CTRL+F, choose find it Current Project and look for all instances of "msg_Send" and also check where gInteger gets set.

Cheers,

[Eug7]

Vlad, the only location is in the class module for "msg_Send" that also includes the below. I'm speculating here but maybe the below has something to do with being tied to the opened Outlook instance.


Option Compare Database
Public WithEvents app As Outlook.Application
Public WithEvents msg As Outlook.MailItem


Private Sub Class_Initialize()


Set app = CreateObject("Outlook.Application")
Set msg = app.CreateItem(olMailItem)


End Sub