Solved

trouble with snp format for access reports

Posted on 2008-10-22
18
582 Views
Last Modified: 2013-11-28
Hi experts! I have an access report -- actually an invoice that I generate and email to clients.  I chose to create this in snapshot format - and it looks beautiful.  recently I had a client say that they couldn't open it -- they didn't specify what the problem was... and they wanted it as a word doc.  I can't understand what they problem could be... isn't snapshot viewer part of windows??
in any event, I tried generating the report in "rich text format" and it messed up the formatting - basically the thing looked awful (even had a field on there several times - it was a mess).  any ideas? is there some way I could make the snp format even more user friendly? or some type of instructions or suggestions I could forward to clients with the invoice to explain how to open it?
thanks!
0
Comment
Question by:jpomerantz
  • 10
  • 8
18 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
jpomerantz,

The client must download or install the "Snapshot" veiwer.

It is part of Office, not Windows, so it is on the Office install disks.
I think it only gets installed on "Complete installations"

JeffCoachman
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
Sorry, I forgot the rest of your question...

If they install the viewer and all is well, then fine.

Because Access Reports and Word Documnets display data in different ways, simply saving the Report as RTF will not assure that ther Word Doc will be identical to the Report.
(Especially with reports with a lot of formatting)

Your other option is to output the Reports to the PDF format.
In Access 2007 this is easy, you just download the PDF Add-in, and it will appear when you select "Save As".

In Access 2003 and older you need a utility like:
http://www.cutepdf.com/
or
http://www.lebans.com/reporttopdf.htm

JeffCoachman
0
 

Author Comment

by:jpomerantz
Comment Utility
cool! the pdf is definitely my best option I think - what a cool program... I downloaded the cutepdf software and it seems to work fine (from a word doc etc..) but how do I get pdf to show up as one of the "output formats" in my send object macro?? is there a way to do that or work around it? my goal here is to send the invoice to the client directly attached to an email...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
jpomerantz,

   "but how do I get pdf to show up as one of the "output formats" in my send object macro"
You can't, you have to create an email manually.

Use code like the sinppet below:

The code is commeted, so you can figure out all the options.

JeffCoachman


Dim objOutlook          As Outlook.Application

Dim objOutlookMsg       As Outlook.MailItem

Dim objOutlookRecip     As Outlook.Recipient

Dim objOutlookAttach    As Outlook.Attachment
 
 

' Create the Outlook session.

Set objOutlook = CreateObject("Outlook.Application")

' Create the message.

Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    With objOutlookMsg

        ' Add the To recipient(s) to the message. Substitute

        ' your names here.

        Set objOutlookRecip = .Recipients.Add("Somebody@123.com")   '<--Recipient's name or email address

        objOutlookRecip.Type = olTo

        ' Add the CC recipient(s) to the message.

        'Set objOutlookRecip = .Recipients.Add("CC Recipient Name")

        'objOutlookRecip.Type = olCC

        ' Set the Subject, Body, and Importance of the message.

        .Subject = "This is an Automation test with Microsoft Outlook"      '<--Subject

        .Body = "This is a Test Email"              '<--Email Body text.

        'Use this to send the Email using the HTML Format

        'HTMLBODY = "<HTML><H2><b>This is HTML Text in the BODY of the email</b></H2></HTML>"

        'Insert Variables into the HTML Body

        '.HTMLBODY = "<HTML><b><u>ADDRESS</b></u><br></br><br></br>" & Me.txtAddressLine1 & "<br></br><br></br>" & "<b><u>ADDITIONAL</b></u> <br></br><br></br>" & Me.txtAdditional & "</HTML>"
 

        '.Importance = olImportanceHigh  'High importance

        'Add attachments to the message.

        Set objOutlookAttach = .Attachments.Add("C:\Earth.jpg")             '<-- Add as many attachments as you need here.

        'Set objOutlookAttach = .Attachments.Add("C:\TextFile.txt")
 

'        Reciepts

'        .OriginatorDeliveryReportRequested

'        .ReadReceiptRequested
 

        ' Resolve each Recipient's name.

        For Each objOutlookRecip In .Recipients

            If Not objOutlookRecip.Resolve Then

                objOutlookMsg.Display

            End If

        Next

        

        'Send email without viewing it.

        '.Send

        

        'Dispay email before sending.

        .Display

    

    End With
 

'Cleanup Code

Set objOutlookMsg = Nothing

Set objOutlook = Nothing

Set objOutlookRecip = Nothing

Set objOutlookAttach = Nothing

Open in new window

0
 

Author Comment

by:jpomerantz
Comment Utility
ok cool.  I tried setting things and running it and right away got compile error that said "user type not defined" on this line:

Dim objOutlook          As Outlook.Application

also, it says something in the middle about html that I didn't understand - do I need to do anything there? since I'm trying to generate a report and attach it as a pdf - how do I do that? for now, where it said attachments I just put "reports!Invoice" - i don't even know if that will work - but assuming it would - how would I set the format?? that's how this got started - I'm trying to be able to create and attach this invoice report as a pdf to get around the snp problem... thank you so so much!!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
   "user type not defined"
Oh, you have to set a reference to the Microsoft Outlook Library in your VBA Editor.
  Open the form in design View.
  Press: Alt-F11 to go to the VBA Editor
  Click: Tools-->References
  Scroll down and dheck on the box for: Microsoft Outlook Object Library.

All you should need to do is add the Path, Full File name and extension of your PDF file.
So the Attachment line should read something like:
          Set objOutlookAttach = .Attachments.Add("C:\YourFolder\YourPDF.PDF")

JeffCoachman
0
 

Author Comment

by:jpomerantz
Comment Utility
thanks so much - I'll try adding that reference.  I don't think I was communicating this clearly, but what I'm trying to do is to generate the report and send it automatically from access... I had this set up using a SendObject macro set to the report in question -- but it only allows snp or rtf formats etc... as a work around I could continue to use the same method and get the user to create a pdf out of it using the program that you taught me about (by the way -- I can't thank you enough for showing that to me - it is soooo cool!!) - but ideally, I'd love to be able to have the user just push a button to send the report -- already in pdf format - attached to an email.  is this doable??
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility

Please explain in detail what you mean by :
  "have the user just push a button to send the report -- already in pdf format - attached to an email."

Send the report to WHO?
There is no mention of where the email adress are stored in this database?
Do you have Access installed?

Please list the complete steps a user would have to perform in order to do this.


Anything is doable, but it seems like you leave out certain key points of your objectives of the original question until the last moment?
Forcing me to change my approach and/or redo my samples/Code.
:-O

Let me know.

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
... meaning I don't mind helping...
...just give me everything up front.
;-)

JeffCoachman
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:jpomerantz
Comment Utility
so sorry about that... I just re-read my posts and realized that I really was not clear enough at the beginning on this one.  I can't thank you enough for your help with this stuff!
ok - here's the full story: the goal is for the user to create and send an ivoice to thier customer as an attachment to an email.  the invoice is a regular report that is generated based on criteria selected on an "invoice creation" form (such is client name etc).  on this form, i have a command button set up called "email invoice" and a combo box for them to select  the recipient (the names show up but the bound column is the email address of the recipient).  when they click the command button in executes a SendObject macro.  in that macro the object is the name of the report ("invoice") and the "To" field is set to the value selected as the recipient in the combo box I mentioned earlier (something like: "forms!createinvoice!combo123")
the issue is that the "output format" can only be set to a limited number of things... the snp format is the only one that really maintains the formatting and all that.  - but clients have apparently had problems opening the snp.  as a workaround I told the user to generate the invioce as snp (or just open it as a report) and create a pdf as you suggested earlier.  this works - but it would be really cool if I could somehow allow the user to send the report attached to an email streight from access - but in pdf format.  
that should be the full story. I'm so sorry that I didn't fully explain this the first time. the good news is that I learned some really interesting things here along the way -- I really really appreciate it Jeff!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
working on this now
0
 

Author Comment

by:jpomerantz
Comment Utility
thank you SO much!!
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
Comment Utility
Try this.

Notes:

- Enter you own email for the first customer to test the sample.

- The code to convert the Report to a PDF is basically from Stephen Lebans (www.lebans.com)
So it might be better if you tried moving you objects into this database to test.

- You need to add a reference to the Outlook Object model in your VBA editor.

- Anyone opening your PDF's will need at least Version 6.0 of the Adobe acobat reader (Current version is 9.0)

- You will need to create a folder to store the PDF's (C:\PDF_Folder)

- In the sample, I am saving each PDF, every time an email is sent.
You can modify it to use the same name, and simply overwrite the existing file.

JeffCoachman
Access-EEQ23838456SelectCustomer.mdb
0
 

Author Comment

by:jpomerantz
Comment Utility
wow! thank you so so much! - it looks like this is exactly what I'm trying to do... I think I'm going to need a bit more help though to get it to work.  I entered my own email address in tblCustomers, and created a folder in C: called PDF_Folder, and created the reference as you taught me earlier.  when I go to try to send from the form (after selecting the first recipient, with my email addres) first it asks me for a parameter (tblCustomers.Country) then it says "sorry... cannot find the DynaPDF.dll file please copy to windows system 32 folder" - or something like that. then I get a warning from outlook that someone's trying to access my email addresses, and then I get runtime error 287 that says application defined or object defined error.. when I select debug it highlights the following line:
Set objOutlookRecip = .Recipients.Add(Me.cboCompany.Column(2))   '<--Recipient's name or email address

I'm assuming that I'm doing something wrong :-) thank you so so much for your time and patience! I really appreciate!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
jpomerantz,

"it asks me for a parameter (tblCustomers.Country)"
Oh, sorry, just delete the Customer Country field from the report.

"it says "sorry... cannot find the DynaPDF.dll file please copy to windows system 32 folder"
Go here and download the file.
Copy the two .dll files to the same location as the database or to your Windows\System32 folder
http://www.lebans.com/reporttopdf.htm

"I get a warning from outlook that someone's trying to access my email addresses"
What is the *Exact* message, it might be Outlook or some external program. What version of Office are you running?

"error 287 that says application defined or object defined error.. when I select debug it highlights the following line:
Set objOutlookRecip = .Recipients.Add(Me.cboCompany.Column(2))   '<--Recipient's name or email address"
Not sure why you are getting this error, it worked finr for me.
Perhaps it has something to do with your Office version.

JeffCoachman

.CreateRecipient("someone@somewhere.net")

0
 

Author Comment

by:jpomerantz
Comment Utility
ok cool - I'm going to work on this soon - I'll keep you posted :-) thanks again so much
0
 

Author Closing Comment

by:jpomerantz
Comment Utility
works like a charm now! - I also really really appreciate your referring me to the pdf utility - that is SUCH a handy peice of freeware :-)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
;-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now