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,366 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
  • 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 84
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 84
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 84

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Find out what you should include to make the best professional email signature for your organization.
Read this checklist to learn more about the 15 things you should never include in an email signature.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

825 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