send email without using a macro

[Jeffrey Tan]

@ADezii:

Yep still experiencing that report problem.

It's attaching all the records instead of the specific record i'm on.

I can't seem to figure what I did wrong.

This is my query that the report is based on

SELECT user_problem_log.*, usr_problem_list.trouble_no, usr_problem_list.date, 
usr_problem_list.user, usr_problem_list.notes, users.First_Name, users.Last_Name, 
users.extension, offices.Office_Name, users.group_name, users.email
FROM user_problem_log 
INNER JOIN ((offices INNER JOIN users 
ON offices.office_number = users.Office) 
INNER JOIN usr_problem_list ON users.win_id = usr_problem_list.user) ON user_problem_log.trouble_no = usr_problem_list.trouble_no;

I designed the report the way I normally do:
- label in report header
- rest of the fields in Detail

[Jeffrey Tan]

Anyway to incorporate this in the code?

[user_problem_log].[trouble_no]=[Forms]![User Problem Log]![Text30]

Text30 is the autonumber field.

The above code is taken form the print macro

Report Name: Sub Tickets-Print via Form
View: Print Preview
Filter Name:
Where Condition: see above
Window Mode: Normal

I believe the reason why the code to attach the report attached everything is because access doesn't know which report it is so it grabs every record?

[ADezii]

@Jeffrey Tan:
As previously indicated by NeoPa, this seems to be a purely SQL issue as opposed to VBA. The addition of a WHERE Clause in the SQL Statement may do the trick.

[Jeffrey Tan]

@ADezii

Yeah I don't know where to include that part and somehow tie it along with the report name in the strReportName. This is the part I'm really confused on. :(

[Jeffrey Tan]

OMG LOL I think I got it.

I just added that where clause in the query that the report uses!

I'll try that again and see if it really does work.

The last question I have...

when the attachment of the report is attached, it takes the name of the report name... so in my case it attaches it as

"Sub Email Single Ticket.htm (5kb)"

Can we somehow change the name of that to something else? Thanks a bunch! Really appreciate the help!!

-edit-
nvm got it :D

[ADezii]

I just added that where clause in the query that the report uses!

Correctamundo, that's what I was referring to.

Can we somehow change the name of that to something else? Thanks a bunch!

The following Code will provide the Attached Report with a Unique Name based on a Base Name, the Current Date, and the Current Time. Pay special attention to Code Line Numbers: 10, 21, 25 and 31:

'Provides the Send Mail automation. Send an E-Mail and Attachment from
'Access via Outlook
Dim oLook As Object
Dim oMail As Object
Dim olns As Outlook.NameSpace
Dim strRecipient As String
Dim strBody As String
Dim strSubject As String
Dim strReportName As String
Dim strRptAttachName As String
 
Set oLook = CreateObject("Outlook.Application")
Set olns = oLook.GetNamespace("MAPI")
Set oMail = oLook.CreateItem(0)

'************************** Define your own Values here **************************
strRecipient = "ADezii@aol.com"
strBody = "To be or not to be, " & Chr$(13) & Chr$(10) & "that is the question"
strSubject = "Demo Database for Jeffrey Tan"
strReportName = "rptEmployees"
strRptAttachName = "JTs_Report_" & Format$(Now, "mmddyyyy_hhmmss")
'*********************************************************************************

DoCmd.OutputTo acOutputReport, strReportName, acFormatHTML, CurrentProject.Path & _
               "\" & strRptAttachName & ".htm", False
 
With oMail
  .To = strRecipient
  .Body = strBody
  .Subject = strSubject
  .Attachments.Add CurrentProject.Path & "\" & strRptAttachName & ".htm", False
    .Display
End With

Set oMail = Nothing
Set oLook = Nothing

BTW, how did the vbNewLine substitution work out? You may also want to incorporate Code into the Logic that will DELETE the Report as formatted in HTML after Processing/Attaching it.

[Jeffrey Tan]

@ADezii

I just used the following for the report name attachment

strReportName = Mid("Email-Single", 7) & " Problem Tracking Ticket # " & [Forms]![User Problem Log]![trouble_no]
  
DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatHTML, CurrentProject.Path & _
               "\" & strReportName & ".htm", False

learned it from another question i asked before :D

And yes the Chr$10 and 13 worked out perfectly! Very nice :D Will keep that one in mind.

Yeah I will have to add code after it attachs the report to delete the HTML file. Is it pretty easy to do that?

-edit-
or how about when it attaches the report, move it to the user's temp folder and let windows deal with it :)

[ADezii]

Yeah I will have to add code after it attachs the report to delete the HTML file. Is it pretty easy to do that?

1. Be careful not to DELETE the File until after it has been Sent.
2. You can DELETE ALL HTML (*.htm) Files in the Currentproject. Path at any time, via:
   

   'DELETE ALL HTML (*.htm) Files in the CurrentProject.Path Directory
   If Dir$(CurrentProject.Path & "\*.htm") <> "" Then Kill CurrentProject.Path & "\*.htm"

[Jeffrey Tan]

@ADezii

what do you mean by #1. Isn't the html file we created the only to be deleted?

I tried to use the following to move the file to another folder, but I get an error saying the file already exists

Name CurrentProject.Path & "\" & strReportName & ".htm" As "C:\Documents and Settings\jtan\Desktop\folder"

-edit-

silly me. left out the file name at the end of the new directory. should be

Name CurrentProject.Path & "\" & strReportName & ".htm" As "C:\Documents and Settings\jtan\Desktop\folder" & strReportName & ".htm"

[ADezii]

Dim strSource As String
Dim strDestination As String

strSource = CurrentProject.Path & "\"
strDestination = "C:\Documents and Settings\jtan\Desktop\"

Name CurrentProject.Path & "\" & strReportName & ".htm" As strDestination & strReportName & ".htm"

[Jeffrey Tan]

@ADzeii

Thanks! I guess this problem is now solved :D

You guys are great!!

[Jeffrey Tan]

Hmm another problem now :P

Now what happen's if there are two or more pages of a report? :D

-edit-
the ones with two pages of a report are named with Page2.htm, Page3.htm etc with the report name preceding...

[Jeffrey Tan]

now converting them to a PDF would solve the problem, but I'm not sure if there's such thing as acFormatPDF

[ADezii]

but I'm not sure if there's such thing as acFormatPDF

I think that Access 2007 has this option, but if not there are always the *.rtf and *.snp Formats. To me, the *.snp (Snapshot) Format is very impressive. I'm not positive, but I think that the Snapshot Viewer will be installed on Client PCs on first time usage. Rich Text Format is pretty much universal, and usually displays quite well from what I have seen.

[Jeffrey Tan]

@ADezii,

I tried rtf but i dont think it works.

As for SNP, it does work (puts multiple reports in one .snp file), however, ms outlook security disables viewing any .snp file :(

I was thinking to set a count of how many files there are for one specific record and add it to a counter. say if there were 4 pages of record #1, then four would be added and then it would do the
.Attachements.A dd ..... Page1.htm until it goes to the last page.

-edit-

hrm.. RTF does work.. but i think in the long run PDF will be best. i did a bit of research and to get ACFORMATPDF to work for 2003, thre is a lot of work to be done :(