07-18-2013 07:38 AM
Has anyone had any experience with getting "Email Sent" history recorded when using CreateObject("Outlook.Application") in Excel VBA?
I have developed a bill sending application for a client using Excel VBA and whilst the actual application works well and sends the emails out via the local copy of Outlook, the history is not being recorded with the send. If I create the email manually in Outlook then it works.
I don't fully understand the difference. Your advice would be appreciated.
07-18-2013 05:24 PM - edited 07-18-2013 05:26 PM
Try using the the UIEmailManager from inside the ACTApplication. Here is a function we use in Impact Suite..
It will send theemail and also record a history.
Public Shared Function SendEmailViaACT(ByVal HostApplication As Act.UI.ActApplication, _ ByVal cContact As Act.Framework.Contacts.Contact, _ ByVal sMessageBody As String, _ ByVal sEmailSubject As String, _ ByVal ACTHistType As Object, _ ByVal lReturnReceipt As Boolean, _ ByVal sAttachments As String, _ ByVal lIsPrivate As Boolean, _ ByVal lPreview As Boolean, _ ByVal EmailAddressField() As String) As Boolean Try If cContact Is Nothing Then LogError.Write(New System.Exception("EmailDocument: Contact can not be empty"), "EmailDocument: Contact can not be empty") Return False End If Dim mFilesToAttach = New System.Collections.Specialized.StringCollection() If sAttachments = String.Empty Then mFilesToAttach = Nothing Else mFilesToAttach.Add(sAttachments) End If '------------------------------------------------------ ' Are we previewing before sending the email '------------------------------------------------------ If lPreview = True Then HostApplication.UIEmailManager.CreateEmailDraft(cContact, _ "FIELDNAME", _ sMessageBody, _ sEmailSubject, _ ACTHistType, _ lReturnReceipt, _ mFilesToAttach) Else HostApplication.UIEmailManager.SendEmailToContact(cContact, _ sMessageBody, _ sEmailSubject, _ ACTHistType, _ lReturnReceipt, _ lIsPrivate, _ mFilesToAttach, _ EmailAddressField) End If Return True Catch ex As System.Exception LogError.Write(ex, "Email:SendEmailViaACT") End Try Return False End Function
Hope this helps
-- Jim Durkin
07-19-2013 09:57 AM
Unfortunately I can't use anything within ACT itself. This code must exist within the VBA of the Excel workbook and I see no references available in VBA to your function.
The hunt is still on.
07-22-2013 06:38 AM
Since this doesn't appear to be using ACT, your best bet would probably be to post your issue on an Outlook (or Office) based forum. A quick search of what you are trying to do got me this link http://msdn.microsoft.com/en-us/library/office/ff458119(v=office.11).aspx, which goes through the basic process. You may want to look through the Outlook model reference as well (http://msdn.microsoft.com/en-us/library/office/bb208225(v=office.12).aspx). I'm not worrying about which version of Outlook in these links, I just grabbed the first one that came up. Hopefully this will help you out.
07-22-2013 03:17 PM
Caveat to my advise: it's been a long time since I've written in VBA, so grain of salt...
I think Jim was assuming you would instantiate an instance of the Act framework from within your VBA code using the CreateObject() function.
Once you create an instance of the ActFramework you can call the framework function SendEmailViaACT()....
I don't remember how to get a reference into VBA, but I believe that's what you'll need to do to be able to create the Object from VBA.
07-22-2013 04:10 PM - edited 07-22-2013 04:51 PM
I went to play with VBA but I did not know the COM object name.
Are any of the act! dlls exposed as a COM object?
Caveat: SendEmailViaACT is a function of the Impact Suite add-on. To send email using the ACT you call the HostApplication.UIEmailManager.SendEmailToContact which is part of the act! application.
I seriously doubt you can initial the entier act! application in VBA. You may be able to initiate the actFramework but I did not find a reference to the email manager in the framework.
Interested question: Could this be done with a custom DLL written in VS which is exposed to COM?
It basically dropps txt files into a folder to perform an action. They may have the solutions to generate histories without using createObject.
You should look into xData. It has it own set of APIs
-- Jim Durkin
07-24-2013 08:39 AM
You need to build a COM Callable wrapper around the framework assemblies you want to reference then ref the wrapper, pretty straight forward how to do that - tons of examples for interop purposes.
Once you get the framework you could presumably directly write the history, etc.. as needed.
Sorry didn't realize that funciton was one of yours. should have known though as the out of box funcitons are not so concisely named