Solved

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

Posted on 2011-03-11
17
1,291 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
17 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35110306
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
ID: 35110353
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
ID: 35110638
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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35110777
What do you use for the AcSendObjectType parameter in those other mail calls?
0
 
LVL 85
ID: 35111891
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
ID: 35127564
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
ID: 35127746
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
ID: 35127763
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35127808
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
ID: 35128315
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
ID: 35128699
>>>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
ID: 35129855
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
ID: 35131377
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
ID: 35136459
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
ID: 35137064
>>> 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
ID: 35137129
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
ID: 35137157
Glad I was able to help that little bit.

Chris
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Changing a few Outlook Options can help keep you organized!
This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

691 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