Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
1,482 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 28

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 28

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 28

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 28

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

By default Outlook 2016 displays only one time zone in the Calendar. The following article explains how to display two time zones in one calendar view.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

972 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