Solved

Two problems I'm having sending e-mails with  DoCmd.SendObject in Access

Posted on 2011-03-11
17
1,179 Views
Last Modified: 2012-05-11
I am developing an Access 2007 "project" (.ADP) as the front-end to a SQL Server 2005 Express database.

From within the Access application I am using some VBA code to send e-mails with ".pdf" attachemnts as below :

DoCmd.SendObject acSendReport, strReportName, acFormatPDF, _
                                                                strAddrEMail, "", "", _
                                                               strEMailSubject, strMessText, False

I have two problems :

1. The text for the "body" of the e-mail is passed in via "strMessText" and appears in the e-mail OK, but the e-mail body also contains the characters "<<...>>" in the first line of the e-mail, above my intended text. Can anyone tell me why this is, and how I can get rid of it?

2. This code is used to issue several e-mails to different customers in a single session, but for each e-mail I get the Outlook security warning telling me an e-mail is being sent, giving the options to "Allow" or "Deny". This is not a great problem when only a couple of e-mails are being sent, but a real pain when dozens are being issued.
From the Google results I have found, this does not appear to be easily removed and requires some VBA coding in each Outlook instance being used. Does anyone have a simpler solution to this, so that the security warning can be by-passed?

Many thanks. Colin.
0
Comment
Question by:colinasad
  • 10
  • 6
17 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
For the mail wrapper with <<>> do new mails in outlook have this?

In re the security warning then:

1. You can modify your code to use redemption: | http://www.dimastr.com/redemption/
2. You can use an outlook add-in i.e: | http://www.mapilab.com/outlook/security
3. Or finally you can use clickyes | http://www.contextmagic.com/

From my perspective ... if you do a lot of VBA programming then redemption is a good solution ... personally I rarely use it now.
I now use the maplab security add-in of option 2.  The first time you do something it asks if you want to continue, and if you click the always take this option it never bothers you again ... and it's free for both personal and commercial usage.
3. Personally I have never used clickyes but there are those who advocate so I include it for information

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Not sure about, acSendReport have you tried acSendNoObject instead?  ANd retinking the security question you mention multiple clients so redemption would be the answer unless you can get every one of those pc's with the mapilab security add-in.

Chris
0
 

Author Comment

by:colinasad
Comment Utility
Thanks for the prompt responses, Chris.

I do not get the "<<...>>" characters when I use Outlook to type new e-mails.
Strangely, elsewhere in my Access application I sometimes allow the user to type a message before using the same "DoCmd.SendObject" method and these emails do not contain the "<<...>>" characters.
I don't know where they are coming from in this instance. I have used "Trim" on my strMessText variable and checked it in a Message Box before the "DoCmd.SendObject" call, and can see nothing strange with the text.

I will investigate your suggestions for my Outlook Security issue.

Thanks. Colin.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
What do you use for the AcSendObjectType parameter in those other mail calls?
0
 
LVL 84
Comment Utility
If you're going to look into 3rd party items, then I'd suggest reviewing vbMAPI from www.everythingaccess.com. It does everything Redemption does without the need to deploy anything separate from your Access database.
0
 

Author Comment

by:colinasad
Comment Utility
I have downloaded and installed the MAPILab "Advanced Security for Outlook" add-in, but am none the wiser and no further forward.
Certainly, the "Advanced Security for Outlook" splash screen now appears when I launch Outlook and there is a new "Advanced Security" option offered in my Outlook "Tools" menu. My reading of the "Help" text (or as much of it as I could understand) suggests that actions (eg allow, block) can be set for specific applications. I just cannot see where that is done.

When I try sending e-mails from within my Access .adp "project" I still encounter the Outlook Security message asking me if I want to "Allow" or "Deny" the request to send an e-mail. I have not been able to find any setting that I hoped would by-pass this message, allowing Access to always send e-mails directly.

Has anyone used the MAPILab "Advanced Security for Outlook" add-in and can point me in the right direction?

I also downloaded and installed the CDO Library for Outlook 2007. The MAPILab software at first recognised that CDO.DLL was not installed, but now acknowledges that it is installed, but with the "Installation result" message "This version have no Security Guard" (sic).

Again, I must confess I don't realy know what I'm doing by installing the CDO.DLL, but it seemed like it might help.

Any further help gratefully received.
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
Comment Utility
Thn if the library does not pick up on sendobject, (didn't realise that) then you will need to replace sendobject with specific code for example as in the snippet.

NewOutlookMail "Test Mail", "Sent using Outlook", "chris@domain.net", "chris@domain.com", "<<< ### PATH TO THE PDF ### >>>", True, true

Chris
Function NewOutlookMail(strSubject As String, strBody As String, Optional strTo As String, _
                            Optional strCC As String, Optional strBCC As String, Optional SendYN As Boolean = False, _
                            Optional AttachYN As Boolean = False, Optional Attach1 As String) As Boolean
Dim olkApp As Object

    Set olkApp = CreateObject("outlook.application")
    With olkApp.CreateItem(0)
        .To = strTo
'        .replyto = "fred@jdomain.com"
        If strCC <> "" Then .CC = strCC
        If strBCC <> "" Then .BCC = strBCC
        .Subject = strSubject
        .body = strBody
        If AttachYN And Attach1 <> "" Then .Attachments.Add Attach1
        If SendYN Then
            .Send
        Else
            .Display
        End If
    End With

End Function

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Note with the mapiliab add in already installed this WILL still trigger the alert first time around BUT after clicking accept all in the add-in the it will not happen subsequently.

Chris
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Oops!

The command line is the wrong version:

NewOutlookMail "Test Mail", "Sent using Outlook", "chris@domain.net", "chris@domain.com", , False, True, "<<< ### PATH TO THE PDF ### >>>"

Chris
0
 

Author Comment

by:colinasad
Comment Utility
Thanks, Chris.
Will this work with my example where I am sending an Access Report, in PDF format, as an attachment?
In my VBA code, I pick up an "Email To" string from a field in the appropriate address record in the database.
I open my report, then if I have an e-mail address to use I execute

DoCmd.SendObject acSendReport, strReportName, acFormatPDF, ....

to send the Report as an attachment. If the e-mail address is blank I execute a
     
DoCmd.RunCommand acCmdPrint

instead, and just print the report.

Then I Close the report.

Would I have to save my report as a PDF file first, then send that file as an attachment using your code?                                    
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
>>>Would I have to save my report as a PDF file first, then send that file as an attachment using your code?

Yes but it can of course be anywhere even in the temp folder as log as teh path in the Attach1 parameter points straight to it.

Chris
0
 

Author Comment

by:colinasad
Comment Utility
Thank you Chris.
Using your "NewOutlookMail" sub procedure rather than "DoCmd.SendObject" seemed to work. I did not receive any prompt asking me if I wanted to allow Access to send e-mails via Outlook, it just allowed it.
I was expecting the MAPILab software to make this allowance on a program-by-program basis. Does this mean that any other program (including a spam virus) could now send e-mails via my Outlook program?

Regards. Colin.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
You have to explicitly allow the application so in this case it is excel.  If a macro virus were to be accepted by you onto your access installation and allowed to run then yes it would send emails in this way ... pretty much the same as sendobject would be able to!

Chris
0
 

Author Comment

by:colinasad
Comment Utility
Hi Chris, thanks for sticking with me on this, even though we seem to have solved my original problem.

In the first sentence of your last post you mention "excel"; did you mean "Access"? I am using Access rather than Excel to send my e-mails.

I'm not aware that I did "explicitly" allow Access to send my e-mails. Once I started to use your "NewOutlookMail" procedure, there was no Security intervention of any kind asking me if I wanted to allow Access to send the e-mails; they were just immediately put into my Outbox. I was expecting the MAPILab software to ask me at least once whether I wanted to grant this permission to Access.

I'm not at that machine until later this afternoon but will have a further poke around the MAPILab screens to see if anything has changed since I started to use your procedure.

Many thanks. Colin.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
>>> In the first sentence of your last post you mention "excel"; did you mean "Access"? I am using Access rather than Excel to send my e-mails.

Yes I meant access though I could have used excel in the context ... same applies

>>> I'm not aware that I did "explicitly" allow Access to send my e-mails. Once I started to use your "NewOutlookMail" procedure, there was no Security intervention of any kind asking me if I wanted to allow Access to send the e-mails; they were just immediately put into my Outbox. I was expecting the MAPILab software to ask me at least once whether I wanted to grant this permission to Access.

I'm guessing you are using outlook 2007 or later ... office takes a more integrated approach to permissions - I wasn't aware that sendobject was excluded from that 'grown up' thinking.

>> I'm not at that machine until later this afternoon but will have a further poke around the MAPILab screens to see if anything has changed since I started to use your procedure.

I'm thinking not based on the previous data point

Chris
0
 

Author Closing Comment

by:colinasad
Comment Utility
Thanks for your help Chris.
In conjunction with the MAPILab "Advanced Security for Outlook" add-in, your method of creating an e-mail appears to have done the trick.
Thanks & regards. Colin.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Glad I was able to help that little bit.

Chris
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now