UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Email Report From Command Button, Access 2016    
 
   
bredmell
post Jan 19 2018, 09:47 AM
Post#1



Posts: 358
Joined: 10-May 03
From: Massachusetts


Morning everyone. I am attempting to "up my game" by trying something I've never done before. I have a user for a DB that I created that currently produces a weekly report (using a form that sets parameters for date start, date end and report created by), manually saves it as a PDF and then attaches it to an email. He would like me to automate this process. I found this post in the archives:

email report

This is the code from that post:

CODE
Public Sub SendReport()
On Error GoTo Whoops
  
  'declare variables
  Dim ol As Outlook.Application, msg As MailItem, atts As Attachments, strFilePath As String
  Set ol = CreateObject("Outlook.Application")
  
  'prepare the attachment
  strFilePath = "\\servername\foldername\reportname.pdf" ' edit this - it's where you will store the report
                         ' and then retrieve it to attach it to the email.
  DoCmd.OutputTo acOutputReport, "report name (edit this)", acFormatPDF, strFilePath, False
  DoEvents
  
  'prepare the message
  Set msg = ol.CreateItem(olMailItem)
  Set atts = msg.Attachments
  With msg
    .Subject = "subject line" 'edit this
    .Body = "message" 'edit this
    .To = "recipient(s)" 'edit this
    .CC = "more recipients" 'edit this
    atts.Add strFilePath
    .Send
  End With
  
  'clean up
Offramp:
  If Not msg Is Nothing Then Set msg = Nothing
  If Not ol Is Nothing Then Set ol = Nothing
  Exit Sub
  
  'error handler
Whoops:
  Select Case Err
    Case Else
      MsgBox "Error #" & Err & ": " & Err.Description
      Resume Offramp
  End Select
End Sub


I have changed it to an On_Click event for a command button, edited it for FilePath-ReportName-etc, compiled it and then tried to execute it. It creates the file in a PDF format in the folder that I specify but then fails to create an email. I get this message that pops up - Error#-2147024843: The operation failed. I have been struggling with this for two days now. Any suggestions, anyone?


--------------------
but they who wait for the LORD shall renew their strength; they shall mount up with wings like eagles
Go to the top of the page
 
DanielPineault
post Jan 19 2018, 10:03 AM
Post#2


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



Why not use a reusable function to send your e-mails, such as: http://www.devhut.net/2010/09/03/VBA-send-...ook-automation/ or at the very least, you can review the code to rework yours.




Also, what line is flagged as problematic when you debug your code?


--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
bredmell
post Jan 19 2018, 10:10 AM
Post#3



Posts: 358
Joined: 10-May 03
From: Massachusetts


Daniel,

Unfortunately, the link you provided sends me to a page that says it can't find what you're looking for.

And the codes debugs fine. No errors indicated at that point.

Bruce

--------------------
but they who wait for the LORD shall renew their strength; they shall mount up with wings like eagles
Go to the top of the page
 
DanielPineault
post Jan 19 2018, 10:21 AM
Post#4


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



http://www.devhut.net/2010/09/03/VBA-send-...ook-automation/ (having trouble crazy.gif with links since the text editor was recently changed)




Your outlook work fine? You can send the email manually just fine?

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
bredmell
post Jan 19 2018, 10:25 AM
Post#5



Posts: 358
Joined: 10-May 03
From: Massachusetts


Yes, Outlook works just fine. I just tested it by manually attaching the file that the above code created.

Bruce

--------------------
but they who wait for the LORD shall renew their strength; they shall mount up with wings like eagles
Go to the top of the page
 
DanielPineault
post Jan 19 2018, 10:34 AM
Post#6


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



I just tested your code and it ran fine for me, message sent and received?! iconfused.gif

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
bredmell
post Jan 19 2018, 10:45 AM
Post#7



Posts: 358
Joined: 10-May 03
From: Massachusetts


That tells me that there doesn't appear to be a problem with the code. Not sure why it won't work for me. I'm going to check with my IT admin and see if there are any internal settings that might be preventing this from working.

Bruce

--------------------
but they who wait for the LORD shall renew their strength; they shall mount up with wings like eagles
Go to the top of the page
 
DanielPineault
post Jan 19 2018, 10:53 AM
Post#8


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



Does simple automation (without an attachment) work?

Does SendObject work?



--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
bredmell
post Jan 19 2018, 11:19 AM
Post#9



Posts: 358
Joined: 10-May 03
From: Massachusetts


Daniel,

I'm still a reasonable newbie at coding. I've done simple stuff like open a report, open a form, case statements and things of that nature. I'll have to look at SendObject and see what I can do.

Bruce

--------------------
but they who wait for the LORD shall renew their strength; they shall mount up with wings like eagles
Go to the top of the page
 
DanielPineault
post Jan 19 2018, 11:34 AM
Post#10


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



Send it blindly

CODE
Docmd.SendObject acSendNoObject, , , "[email="someone@somewhere.com"]someone@somewhere.com[/email]", , , "Test e-mail", "Just testing with the SendObject method.", False





Generates the e-mail and displays it to the user to manually adjust and send.

CODE
Docmd.SendObject acSendNoObject, , , "[email="someone@somewhere.com"]someone@somewhere.com[/email]", , , "Test e-mail", "Just testing with the SendObject method.", True




--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
bredmell
post Jan 19 2018, 01:03 PM
Post#11



Posts: 358
Joined: 10-May 03
From: Massachusetts


Daniel,

It's working now thanks to the help you provided. Not sure why the above code wouldn't work for me but the SendObject method worked like a charm. Simpler is usually better.

Thank you and have a great weekend.

Bruce

cheers.gif guiness.gif

--------------------
but they who wait for the LORD shall renew their strength; they shall mount up with wings like eagles
Go to the top of the page
 
DanielPineault
post Jan 19 2018, 01:12 PM
Post#12


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



Glad to here. thumbup.gif Any time!

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd June 2018 - 09:13 AM