Community
Showing results for 
Search instead for 
Do you mean 
Reply

Excel VBA Email Integration with ACT

Copper Super Contributor
Posts: 61
Country: Australia

Excel VBA Email Integration with ACT

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.

 

Brett Merriman
ACT! Certified Consultant
Action CRM
Bronze Super Contributor
Posts: 1,231
Country: USA

Re: Excel VBA Email Integration with ACT

[ Edited ]

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

 

Copper Super Contributor
Posts: 61
Country: Australia

Re: Excel VBA Email Integration with ACT

Thanks Jim,

 

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.

Brett Merriman
ACT! Certified Consultant
Action CRM
Nickel Contributor
Posts: 175
Country: USA

Re: Excel VBA Email Integration with ACT

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.

Nickel Elite Contributor
Posts: 937
Country: USA

Re: Excel VBA Email Integration with ACT

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.

 

Bronze Super Contributor
Posts: 1,231
Country: USA

Re: Excel VBA Email Integration with ACT

[ Edited ]

Allen,

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?

 

example: 

CreateObject("ACT.APPLICATION")

Or 

CreateObject("ACT.FRAMEWORK")

 

 

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?

 

You may want to contact http://v8software.com/. They had some interesting stuff using http://middlewaresdk.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

http://community.act.com/t5/Act-Developer-s-Forum/XData-for-ACT-Web-API-for-ACT-Developers/m-p/11677...

 

Thanks

-- Jim Durkin

Nickel Elite Contributor
Posts: 937
Country: USA

Re: Excel VBA Email Integration with ACT

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 Smiley Happy