Solved

Emails won't send via MS Access VBA Code - Getting Error . . . Don't have permissions to perform this task

Posted on 2014-01-15
12
1,420 Views
Last Modified: 2014-01-22
I have Windows 7 and Office 2010, and have a MS Access Database that utilizes VBA Code that I've written that automatically creates a PDF file and attaches to email and sends; since I've upgraded from Windows XP and Office 2007, this does not work now.  It does not create an Email, there is nothing in the Sent, Outbox or DRAFT folders;

I was doing some troubleshooting and commented out the code that creates the attachment and attaches to email, and it then created the email and sent; so it has something to do with the attachment;  I've went into the Options/Trust Center/Automatic Download and deselected the option about "downloading pictures, etc.", but it still doesn't work.

I've also checked the files in the outlook folder on my hard drive to see if they are marked as "Read Only" and they're not.

Here is the code I'm using:
Public Function SendEmailEmpCompletedRptNoErrorsNoAction(ByVal varEmpEmail, varAddlEmail As String)
On Error GoTo Err_Handler
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim AttachmentPath As String
Dim DisplayMsg As String
Dim strAttach1 As String
Dim strAttachTemp As String
Dim strOutputToTemp As String
Dim strAttachCopy As String

DoCmd.Echo False
DoCmd.SetWarnings False

strOutputToTemp = "C:\Windows\Temp\Audit_Completed" & "_" & InquiryNum & ".PDF"

DoCmd.Echo False
DoCmd.SetWarnings False

'''Output to Temp Area (to C Drive)
DoCmd.OutputTo acOutputReport, "rptAudit_Emails_EMP_NoErrorsNoAction", acFormatPDF, strOutputToTemp, False

strOutputToTemp = "C:\Windows\Temp\Audit_Completed" & "_" & InquiryNum & ".PDF"

'Set Attachments (from Temp Dir on C Drive)
strAttachTemp = strOutputToTemp

' Copy to Network
strAttachCopy = "\\Wiw2pwpfle001\data\QA Database\Employee Audit Scorecard System\Audit_Completed\Audit_Completed" & "_" & InquiryNum & ".PDF"

' Variable/Path (to copy from C: Drive to Network Drive Folder for Completed Audits
FileCopy strAttachTemp, strAttachCopy

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

'Create the email message and send
'Set objMessage = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
    .To = varEmpEmail & "; " & "seniorqaebrteam@wp.com"
'    .CC = varAddlEmail
    ' Set the Subject, Body, and Importance of the message.
    .Subject = "Audit Completed With No Errors -- No Action Required as of " & Now() & ""
    .HTMLBody = "Attached you will find a copy of your Quality Audit Scorecard.   If you would like to challenge your audit, your response must be " & _
             "received within 2 business days of the receipt of this message. Challenges received after 2 business days will not be accepted." & _
             "<BR><BR>" & "All challenges must be completed using the proper challenge form found within the QA Audit Challenge Process (QLA02); challenges on the wrong form will not be accepted." & _
             "<BR><BR>" & "Please see your OE for assistance should you have questions on the challenge process.   Do not contact your auditor by phone to challenge an audit." & _
             "<BR><BR>" & "Remember that this audit is a way for us to help you achieve the goals and objectives set by management." & _
             "<BR><BR>" & "Sincerely," & "<BR><BR>" & "Your Programs Quality Audit Team"

    ' Add attachments to the message.
    If Not IsMissing(AttachmentPath) Then
        Set objOutlookAttach = .Attachments.Add(strAttachTemp)
    End If

    ' Resolve each Recipient's name.
    For Each objOutlookRecip In .Recipients
        objOutlookRecip.Resolve
    Next

'        .Save
        .Send

End With

Set objOutlook = Nothing

DoCmd.SetWarnings True
DoCmd.Echo True

''Remove attachments from Temp folder on C drive
Kill strAttachTemp
''Kill strAttach1
''Kill strAttach2
''Kill strAttach3
''Kill strAttach4
''Kill strAttach5

Exit_Handler:
    Exit Function

Err_Handler:
    DoCmd.CancelEvent
    MsgBox Err.Description
    Resume Exit_Handler
End Function

Open in new window



Any suggestions on how to get around this?

Thanks,
gdunn59
0
Comment
Question by:gdunn59
[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
  • 5
  • 4
  • 3
12 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39781667
Put a breakpoint on line 31, then check if strAttachTemp is valid.
0
 
LVL 85
ID: 39781920
Perhaps the PDF is not fully rendered when the code hits the Attach line in your Outlook code?

Insert this line just before that one:

If Dir(strAttachTemp) = "" Then
  Msgbox "Not Found"
End If

Also, I'd create the PDF and then run your email code, and THEN rename/copy/move that PDF. These operations can take time, and if something hiccups during the copy/move operation, your email code will fail. If you go ahead and send the email and THEN copy/move, you have at least accomplished the main goal of the process, and you can then troubleshoot what happened with the copy/move.
0
 
LVL 1

Author Comment

by:gdunn59
ID: 39782777
The same exact code that am I using now, I was using before the upgrade to Windows 7 and Office 2010, and it worked fine prior to these upgrades.  So I don't think the issue is that it is not creating the attachment.  It has something to do with Outlook not liking the email having an attachment.

Thanks,
gdunn59
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 1

Author Comment

by:gdunn59
ID: 39782783
As far as putting a breakpoint on line 31, I've already put a breakpoint at the beginning of the code and did F8 through the entire code, and as soon as it process line 67 (.SEND), then I get the error.

Thanks,
gdunn59
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39782812
As far as putting a breakpoint on line 31, I've already put a breakpoint at the beginning of the code and did F8 through the entire code, and as soon as it process line 67 (.SEND), then I get the error.
Did you verify that strAttachTemp is indeed a valid file name path after line 28?
0
 
LVL 1

Author Comment

by:gdunn59
ID: 39782933
MacroShadow:

Yes, strAttachTemp is valid.  As I mentioned this same code worked prior to upgrading to Windows 7 and Office 2010.

Any other suggestions?

Thanks,

gdunn59
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39783096
Perhaps it's a Windows 7 permission hiccup. Do all users including local Admins have the problem?

Try saving the temp file to a custom folder (not one of windows) and see if it makes a difference.
0
 
LVL 85
ID: 39783154
When you say "valid", do you mean it seems to be a valid path, or that you have confirmed that at the time of execution the File exists, and that you have permissions to use that path/directory?

Don't mean to harp on this, but it looks/feels/smells like a permissions issue, as MacroShadow pointed out earlier - especially since you wrote that this was fine prior to the upgrade.
0
 
LVL 1

Author Comment

by:gdunn59
ID: 39784108
I mean the code creates the PDF and places it in the appropriate location.  As I mentioned before, this all work prior to upgrading to Windows 7 and Office 2010.  Nothing has changed in the database/code.  So it has to be something related to the upgrades.

Thanks,
gdunn59
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39784843
That is what I suggested, did you try it?
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39785191
I'm not sure your code is correct here:
   If Not IsMissing(AttachmentPath) Then
        Set objOutlookAttach = .Attachments.Add(strAttachTemp)
    End If
IsMissing is used to determine if a procedure argument is included, and it's not valid for String types (since those types don't include the necessary flags to define them as "missing").

Essentially, you're asking if AttachmentPath is included in the set of "arguments" included with your Procedure:

Public Function SendEmailEmpCompletedRptNoErrorsNoAction(ByVal varEmpEmail, varAddlEmail As String)

The only two Arguments you have included here are varEmpEmail and varAddlEmail, so the call to IsMissing will always be False.

However you mention that you've set a breakpoint:
did F8 through the entire code, and as soon as it process line 67 (.SEND), then I get the error.
When you did that and stepped through the code as MacroShadow suggested, did that code block execute correctly?

Regardless, the use of IsMissing is not correct and you should change it. If your intent is to confirm that strAttachTemp contains a valid path to an existing document, then just use Dir:

If Dir(strAttachTemp) <> "" Then
   .Attachments.Add(strAttachTemp)
End If

Regarding the fact that it worked before upgrading - we have found many instances where working but invalid code broke when upgrading to newer versions of Access. The OpenReport action was a prime example of that, and bit many, many people who had been using it incorrectly for years (myself included).
0
 
LVL 1

Author Closing Comment

by:gdunn59
ID: 39800638
Scott,

Sorry just now getting back to this, I had to go out of town last week.

Anyway, as you suggested, I changed the attachment code from:

 If Not IsMissing(AttachmentPath) Then
        Set objOutlookAttach = .Attachments.Add(strAttachTemp)
    End If


To:

If Dir(strAttachTemp) <> "" Then
   .Attachments.Add(strAttachTemp)
End If


Worked like a charm.  

Thanks much,

gdunn59
0

Featured Post

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

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…
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

630 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