By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,806 Members | 1,490 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,806 IT Pros & Developers. It's quick & easy.

send email without using a macro

P: 86
Hi. I was wondering if there was a way to send an email without using the Send Object method for a macro. if you use the macro, you will get this warning:



I know in VB .NET you have to import an outlook object or w/e it is and then there were methods that allowed you to edit the To, CC, BCC, subject, etc

I was wondering if there is something similar in VBA to do that.

What i want to do is the following:
1) when user clicks "Send Email" button, it opens up MS Outlook
2) it attaches a report
3) fills out the subject

Thanks!
Aug 1 '11 #1

✓ answered by ADezii

@Jeffrey Tan:
I use similar type functionality all the time, so I created some Code for you that you may find slightly useful. It is relatively simple and uses Automation Code to send an E-Mail and Attachment (Report as HTML) from Access via Outlook. The Code will:
  1. Avoid the Outlook Warning Message.
  2. Automatically fill in the Recipient, Body, and Subject Fields in the Outlook Window.
  3. Saves a specified Report as HTML and subsequently attaches it to your E-Mail.
  4. 'Displays' the Outlook Window as opposed to automatically sending the E-Mail.
  5. May provide a substitute for vbNewLine in order to avoid the problems that you are currently experiencing.
  6. Simply set a Reference to the Microsoft Outlook XX.X Object Library, substitute your own Values in the Variable Block, and then fire away.
  7. Any further questions, feel free to ask.
Expand|Select|Wrap|Line Numbers
  1. 'Provides the Send Mail automation. Send an E-Mail and Attachment from Access via Outlook
  2. Dim oLook As Object
  3. Dim oMail As Object
  4. Dim olns As Outlook.NameSpace
  5. Dim strRecipient As String
  6. Dim strBody As String
  7. Dim strSubject As String
  8. Dim strReportName As String
  9.  
  10. Set oLook = CreateObject("Outlook.Application")
  11. Set olns = oLook.GetNamespace("MAPI")
  12. Set oMail = oLook.CreateItem(0)
  13.  
  14. '************************** Define your own Values here **************************
  15. strRecipient = "JTan@aol.com"
  16. strBody = "To be or not to be, " & Chr$(13) & Chr$(10) & "that is the question"
  17. strSubject = "Demo Database for Jeffrey Tan"
  18. strReportName = "rptEmployees"
  19. '*********************************************************************************
  20.  
  21. DoCmd.OutputTo acOutputReport, strReportName, acFormatHTML, CurrentProject.Path & _
  22.                "\" & strReportName & ".htm", False
  23.  
  24. With oMail
  25.   .To = strRecipient
  26.   .Body = strBody
  27.   .Subject = strSubject
  28.   .Attachments.Add CurrentProject.Path & "\" & strReportName & ".htm", False
  29.     .Display
  30. End With
  31.  
  32. Set oMail = Nothing
  33. Set oLook = Nothing

Share this Question
Share on Google+
68 Replies


ADezii
Expert 5K+
P: 8,636
Do you wish to automatically Send the E-Mail or Display the Outlook Screen?
Aug 1 '11 #2

Rabbit
Expert Mod 10K+
P: 12,366
You can't get around that message if you're trying to use Outlook to send the e-mail. If you're in an enterprise set up and they have SMTP, you could use the CDO object to send the e-mail.
Aug 1 '11 #3

P: 86
@ADezii
good question :P

just display the outlook screen before user hits send email.
Aug 1 '11 #4

P: 86
@Rabbit
i found something that might do it, but i just want to be able to display the email before the user sends it.

** Snip **
Aug 1 '11 #5

P: 86
sorry NeoPa.

----

if im doing this via macro how do i skip a line in the message text field?

Expand|Select|Wrap|Line Numbers
  1. ="Ticket #: " & [Forms]![User Problem Log]![trouble_no] & " -- " & "Brief Problem: " & [Forms]![User Problem Log]![brief_problem]
i tried vbNewLine, but it doesn't work?
Aug 1 '11 #6

NeoPa
Expert Mod 15k+
P: 31,489
If vbNewLine doesn't work that generally indicates one of two things :
  1. The object doesn't support multiple lines period.
  2. The object uses a vbLineFeed character for separating lines.

Both would surprise me in this case as vbNewLine includes a vbLineFeed character within it, and the email body (being the closest match I can think of to your reference 'message text') does, typically, contain/handle multiple lines of text.
Aug 1 '11 #7

P: 86
Yeah neoPa, when I tried vbNewLine, VBA automatically added the [ ] to it. so it must be thinking it's a control or something.

I also have another problem linking a report as an attachment.

the macro opens up a report (Print Single Ticket) and when i try to preview the attachment, the report details for the particular record is not the same. the report somehow picks up the latest record and uses that as an attachment.

I've been scratching my head all over about this. Not sure if it's possible.

Other than that, the macro works fine. Just the attachment of a report that's killing me :)
Aug 2 '11 #8

NeoPa
Expert Mod 15k+
P: 31,489
Jeffrey:
Yeah neoPa, when I tried vbNewLine, VBA automatically added the [ ] to it. so it must be thinking it's a control or something.
That sounds more like SQL than VBA. Where are you seeing this exactly?

I'm afraid I'm not really the man to help with your other issues though. I tend to avoid the macro system in Access.
Aug 2 '11 #9

P: 86
@NeoPa, it's in the macro called 'Send Object'

These are the following fields
  • Object Type: I selected Report
  • Object Name: report name
  • Output Format: HTML
  • To:
  • Cc:
  • Bcc:
  • Subject:
  • Message Text:
  • Edit Message: No (this is where you allow it to preview or not)
  • Template File: not sure about thsi one
Aug 2 '11 #10

NeoPa
Expert Mod 15k+
P: 31,489
As I suspected then. Nothing to do with VBA (VBA is the only place you'd expect VBA constants to be recognised in).

As I said before, I'm not in any position to help you further with your macro work I'm afraid. My best advice would be to avoid macros in all situations. They are clumsy and inflexible and they give you nothing that VBA can't do better in various ways.
Aug 2 '11 #11

P: 86
yeah it's kinda limited in terms of what i can do with it.

the only solution i can think of is to somehow right click on the report print preview > send to > mail recipient > and it'll attach as an attachment. other than that, i will also have to look at the References for outlook and see what i can come up with.
Aug 2 '11 #12

NeoPa
Expert Mod 15k+
P: 31,489
I haven't got much, but this link (Problem with e-mailing from within Access) may get you started at least.

Maybe it's time I started getting into this problem (of emailing from Access). I know it comes up a lot (regularly if not frequently) so I'll see if I can make some time to get better acquainted.
Aug 2 '11 #13

ADezii
Expert 5K+
P: 8,636
@Jeffrey Tan:
I use similar type functionality all the time, so I created some Code for you that you may find slightly useful. It is relatively simple and uses Automation Code to send an E-Mail and Attachment (Report as HTML) from Access via Outlook. The Code will:
  1. Avoid the Outlook Warning Message.
  2. Automatically fill in the Recipient, Body, and Subject Fields in the Outlook Window.
  3. Saves a specified Report as HTML and subsequently attaches it to your E-Mail.
  4. 'Displays' the Outlook Window as opposed to automatically sending the E-Mail.
  5. May provide a substitute for vbNewLine in order to avoid the problems that you are currently experiencing.
  6. Simply set a Reference to the Microsoft Outlook XX.X Object Library, substitute your own Values in the Variable Block, and then fire away.
  7. Any further questions, feel free to ask.
Expand|Select|Wrap|Line Numbers
  1. 'Provides the Send Mail automation. Send an E-Mail and Attachment from Access via Outlook
  2. Dim oLook As Object
  3. Dim oMail As Object
  4. Dim olns As Outlook.NameSpace
  5. Dim strRecipient As String
  6. Dim strBody As String
  7. Dim strSubject As String
  8. Dim strReportName As String
  9.  
  10. Set oLook = CreateObject("Outlook.Application")
  11. Set olns = oLook.GetNamespace("MAPI")
  12. Set oMail = oLook.CreateItem(0)
  13.  
  14. '************************** Define your own Values here **************************
  15. strRecipient = "JTan@aol.com"
  16. strBody = "To be or not to be, " & Chr$(13) & Chr$(10) & "that is the question"
  17. strSubject = "Demo Database for Jeffrey Tan"
  18. strReportName = "rptEmployees"
  19. '*********************************************************************************
  20.  
  21. DoCmd.OutputTo acOutputReport, strReportName, acFormatHTML, CurrentProject.Path & _
  22.                "\" & strReportName & ".htm", False
  23.  
  24. With oMail
  25.   .To = strRecipient
  26.   .Body = strBody
  27.   .Subject = strSubject
  28.   .Attachments.Add CurrentProject.Path & "\" & strReportName & ".htm", False
  29.     .Display
  30. End With
  31.  
  32. Set oMail = Nothing
  33. Set oLook = Nothing
Aug 2 '11 #14

P: 86
@ADezii:

That looks really self explanatory. Thanks! I'll give it a try and report back if I have any problems.
Aug 2 '11 #15

P: 86
@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

Expand|Select|Wrap|Line Numbers
  1. SELECT user_problem_log.*, usr_problem_list.trouble_no, usr_problem_list.date, 
  2. usr_problem_list.user, usr_problem_list.notes, users.First_Name, users.Last_Name, 
  3. users.extension, offices.Office_Name, users.group_name, users.email
  4. FROM user_problem_log 
  5. INNER JOIN ((offices INNER JOIN users 
  6. ON offices.office_number = users.Office) 
  7. INNER JOIN usr_problem_list ON users.win_id = usr_problem_list.user) ON user_problem_log.trouble_no = usr_problem_list.trouble_no;
  8.  
I designed the report the way I normally do:
- label in report header
- rest of the fields in Detail
Aug 2 '11 #16

P: 86
Anyway to incorporate this in the code?

Expand|Select|Wrap|Line Numbers
  1. [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?
Aug 2 '11 #17

ADezii
Expert 5K+
P: 8,636
@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.
Aug 2 '11 #18

P: 86
@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. :(
Aug 2 '11 #19

P: 86
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
Aug 2 '11 #20

ADezii
Expert 5K+
P: 8,636
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:
Expand|Select|Wrap|Line Numbers
  1. 'Provides the Send Mail automation. Send an E-Mail and Attachment from
  2. 'Access via Outlook
  3. Dim oLook As Object
  4. Dim oMail As Object
  5. Dim olns As Outlook.NameSpace
  6. Dim strRecipient As String
  7. Dim strBody As String
  8. Dim strSubject As String
  9. Dim strReportName As String
  10. Dim strRptAttachName As String
  11.  
  12. Set oLook = CreateObject("Outlook.Application")
  13. Set olns = oLook.GetNamespace("MAPI")
  14. Set oMail = oLook.CreateItem(0)
  15.  
  16. '************************** Define your own Values here **************************
  17. strRecipient = "ADezii@aol.com"
  18. strBody = "To be or not to be, " & Chr$(13) & Chr$(10) & "that is the question"
  19. strSubject = "Demo Database for Jeffrey Tan"
  20. strReportName = "rptEmployees"
  21. strRptAttachName = "JTs_Report_" & Format$(Now, "mmddyyyy_hhmmss")
  22. '*********************************************************************************
  23.  
  24. DoCmd.OutputTo acOutputReport, strReportName, acFormatHTML, CurrentProject.Path & _
  25.                "\" & strRptAttachName & ".htm", False
  26.  
  27. With oMail
  28.   .To = strRecipient
  29.   .Body = strBody
  30.   .Subject = strSubject
  31.   .Attachments.Add CurrentProject.Path & "\" & strRptAttachName & ".htm", False
  32.     .Display
  33. End With
  34.  
  35. Set oMail = Nothing
  36. 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.
Aug 2 '11 #21

P: 86
@ADezii

I just used the following for the report name attachment
Expand|Select|Wrap|Line Numbers
  1. strReportName = Mid("Email-Single", 7) & " Problem Tracking Ticket # " & [Forms]![User Problem Log]![trouble_no]
  2.  
  3. DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatHTML, CurrentProject.Path & _
  4.                "\" & strReportName & ".htm", False
  5.  
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 :)
Aug 2 '11 #22

ADezii
Expert 5K+
P: 8,636
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:
    Expand|Select|Wrap|Line Numbers
    1. 'DELETE ALL HTML (*.htm) Files in the CurrentProject.Path Directory
    2. If Dir$(CurrentProject.Path & "\*.htm") <> "" Then Kill CurrentProject.Path & "\*.htm"
Aug 2 '11 #23

P: 86
@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

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1. Name CurrentProject.Path & "\" & strReportName & ".htm" As "C:\Documents and Settings\jtan\Desktop\folder" & strReportName & ".htm"
Aug 2 '11 #24

ADezii
Expert 5K+
P: 8,636
Expand|Select|Wrap|Line Numbers
  1. Dim strSource As String
  2. Dim strDestination As String
  3.  
  4. strSource = CurrentProject.Path & "\"
  5. strDestination = "C:\Documents and Settings\jtan\Desktop\"
  6.  
  7. Name CurrentProject.Path & "\" & strReportName & ".htm" As strDestination & strReportName & ".htm"
Aug 2 '11 #25

P: 86
@ADzeii

Thanks! I guess this problem is now solved :D

You guys are great!!
Aug 2 '11 #26

P: 86
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...
Aug 2 '11 #27

P: 86
now converting them to a PDF would solve the problem, but I'm not sure if there's such thing as acFormatPDF
Aug 2 '11 #28

ADezii
Expert 5K+
P: 8,636
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.
Aug 2 '11 #29

P: 86
@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.Add ..... 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 :(
Aug 2 '11 #30

P: 86
How do I determine the number of files in a directory that have certain words in the file name?

I have this function here
Expand|Select|Wrap|Line Numbers
  1. Function countfiles(folder As String)
  2.  
  3. Dim directory As String, countOf As Long
  4. directory = Dir$(folder & "\*.htm")
  5. Do Until directory = ""
  6.     countOf = (countOf + 1)
  7.     directory = Dir$()
  8. Loop
  9.  
  10. MsgBox countOf
  11.  
  12. End Function
i want to see the number of htm files that have the words "Single Problem Tracking Ticket #9.htm"

some may have "Single Problem Tracking Ticket #9Page2.htm" Page3, etc
Aug 3 '11 #31

ADezii
Expert 5K+
P: 8,636
Pass another Argument to the Function that will be the String to search for in the Filename:
  1. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Function countfiles(folder As String, strStringToSearch As String)
    2. Dim directory As String
    3. Dim countOf As Long
    4.  
    5. directory = Dir$(folder & "\*.htm")
    6.  
    7. Do Until directory = ""
    8.   If InStr(directory, strStringToSearch) > 0 Then
    9.     countOf = (countOf + 1)
    10.   End If
    11.   directory = Dir$()
    12. Loop
    13.  
    14. MsgBox countOf & " File(s) in " & folder & " contain(s) the String [" & strStringToSearch & "]"
    15. End Function
  2. Sample Call:
    Expand|Select|Wrap|Line Numbers
    1. countfiles("<Folder to Search>","Single Problem Tracking Ticket #9.htm")
Aug 3 '11 #32

NeoPa
Expert Mod 15k+
P: 31,489
ADezii:
Be careful not to DELETE the File until after it has been Sent.
Generally, a copy of the attachment is stored within your email database (wherever that is on your system) once it's been attached, so that should make it safe to delete once attached. My experience is mainly with Outlook though, so do test this before relying on it 100%
ADezii:
You can DELETE ALL HTML (*.htm) Files in the Currentproject.Path at any time, via:
Expand|Select|Wrap|Line Numbers
  1. 'DELETE ALL HTML (*.htm) Files in the CurrentProject.Path Directory
  2. If Dir$(CurrentProject.Path & "\*.htm") <> "" Then Kill CurrentProject.Path & "\*.htm"
Just a point to mention that web pages often have associated folders to store any images etc that relate to the page. I don't currently have code that does recursive folder tree deletion, but I may work on one when I get a chance.
Aug 3 '11 #33

NeoPa
Expert Mod 15k+
P: 31,489
ADezii:
Expand|Select|Wrap|Line Numbers
  1. directory = Dir$(folder & "\*.htm")
You may simply want to say (in line #5) :
Expand|Select|Wrap|Line Numbers
  1. directory = Dir$(folder & "\" & strStringToSearch & "*.htm")
Aug 3 '11 #34

ADezii
Expert 5K+
P: 8,636
@NeoPa:
I was thinking more along the lines of Attaching the File, Deleting the File itself, then Canceling the E-Mail. You can now no longer go back to Re-attach the same File since it will no longer exist. Make sense to you, or am I on another one of my Tangents? (LOL).
Aug 3 '11 #35

P: 86
@ADezii:

I will use your code instead for the search string :D

very nice.

As for the acFormatPDF, i gave up on PDF :P will just attach it as X amount of HTM files if report has X amount of pages.

Somehow, somewhere, MS Outlook is referencing the attachement as a shortcut? And when I send the email (to myself to test), MS outlook blocked it....

The code from yesterday (sending only one file) worked...

I don't know what I did wrong that made MS Outlook get a shortcut of the file.

I'll paste the code I worked on here in the next post.
Aug 3 '11 #36

P: 86
The first code is a function to see if a file exists or not
Expand|Select|Wrap|Line Numbers
  1. Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
  2.  
  3.     Dim lngAttributes As Long
  4.  
  5.     'Include read-only files, hidden files, system files.
  6.     lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)
  7.  
  8.     If bFindFolders Then
  9.         lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
  10.     Else
  11.         'Strip any trailing slash, so Dir does not look inside the folder.
  12.         Do While Right$(strFile, 1) = "\"
  13.             strFile = Left$(strFile, Len(strFile) - 1)
  14.         Loop
  15.     End If
  16.  
  17.     'If Dir() returns something, the file exists.
  18.     On Error Resume Next
  19.     FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
  20. End Function
  21.  
The next function is to count the files containing the report name excluding the Page2, Page3, etc
Expand|Select|Wrap|Line Numbers
  1. Function countfiles(folder As String)
  2.  
  3. Dim directory As String, countOf As Long
  4.  
  5. directory = Dir$(folder & "\Single Problem Tracking Ticket # " & [Forms]![User Problem Log]![trouble_no] & "*", vbNormal)
  6. Do Until directory = ""
  7.     countOf = (countOf + 1)
  8.     directory = Dir$
  9. Loop
  10.  
  11. countfiles = countOf
  12.  
  13. 'directory = Dir$(folder & "\Single Problem Tracking Ticket # 9*")
  14. End Function
  15.  
This is the code that sets up the email part
Expand|Select|Wrap|Line Numbers
  1. Sub sendEmail()
  2.  
  3. 'Provides the Send Mail automation. Send an E-Mail and Attachment from Access via Outlook
  4. Dim oLook As Object
  5. Dim oMail As Object
  6. Dim olns As Outlook.NameSpace
  7. Dim strRecipient As String
  8. Dim strBody As String
  9. Dim strSubject As String
  10. Dim strReportName As String
  11. Dim strSource As String
  12. Dim strDestination As String
  13. Dim numofFiles As Integer
  14. Dim attachfile As String
  15.  
  16. Set oLook = CreateObject("Outlook.Application")
  17. Set olns = oLook.GetNamespace("MAPI")
  18. Set oMail = oLook.CreateItem(0)
  19.  
  20. '************************** Define your own Values here **************************
  21. strRecipient = ""
  22. strBody = ""
  23. strSubject = "Problem Tracking Ticket Number: " & [Forms]![User Problem Log]![trouble_no]
  24. strReportName = Mid("Email-Single", 7) & " Problem Tracking Ticket # " & [Forms]![User Problem Log]![trouble_no]
  25.  
  26. '*********************************************************************************
  27.  
  28. 'DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatHTML, CurrentProject.Path & _
  29. '               "\" & strReportName & ".htm", False
  30.  
  31. strSource = CurrentProject.Path & "\"
  32. strDestination = "C:\Windows\Temp\"
  33.  
  34. If Len(Dir(strDestination, vbDirectory)) = 0 Then 'if directory does not exist
  35.  
  36.     MkDir (strDestination) 'create it
  37.  
  38. End If
  39.  
  40. If FileExists(strDestination & strReportName & "*") = False Then 'if file does not exist
  41.  
  42.     DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatHTML, CurrentProject.Path & _
  43.                "\" & strReportName & ".htm", False 'create html file page1, page2, etc
  44.  
  45.     'numofFiles = countfiles("C:\Windows\Temp")
  46.  
  47.     numofFiles = countfiles("C:\Documents and Settings\jtan\Desktop\backup")
  48.  
  49.     If numofFiles > 1 Then
  50.         Dim i As Integer
  51.         For i = 2 To numofFiles
  52.             Name CurrentProject.Path & "\" & strReportName & "Page" & i & ".htm" As strDestination & strReportName & "Page" & i & ".htm" 'move to C:\Windows\Temp 
  53.         Next
  54.             Name CurrentProject.Path & "\" & strReportName & ".htm" As strDestination & strReportName & ".htm" 'move to C:\Windows\Temp
  55.         Else
  56.             Name CurrentProject.Path & "\" & strReportName & ".htm" As strDestination & strReportName & ".htm" 'move to C:\Windows\Temp
  57.     End If       
  58. Else 'if file does exist
  59.     Kill strDestination & strReportName & "*" 'delete it
  60.     DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatHTML, CurrentProject.Path & _
  61.                "\" & strReportName & ".htm", False 'recreate it
  62.  
  63.     numofFiles = countfiles("C:\Documents and Settings\jtan\Desktop\backup")
  64.  
  65.     If numofFiles > 1 Then
  66.         For i = 2 To numofFiles
  67.             Name CurrentProject.Path & "\" & strReportName & "Page" & i & ".htm" As strDestination & strReportName & "Page" & i & ".htm" 'move to C:\Windows\Temp
  68.         Next
  69.             Name CurrentProject.Path & "\" & strReportName & ".htm" As strDestination & strReportName & ".htm" 'move to C:\Windows\Temp
  70.         Else
  71.             Name CurrentProject.Path & "\" & strReportName & ".htm" As strDestination & strReportName & ".htm" 'move to C:\Windows\Temp
  72.  
  73.     End If
  74.  
  75. End If
  76.  
  77. oMail.To = strRecipient
  78. oMail.Body = strBody
  79. oMail.Subject = strSubject
  80.  
  81. If numofFiles > 1 Then
  82.     oMail.Attachments.Add strDestination & "\" & strReportName & ".htm", False
  83.     For i = 2 To numofFiles
  84.         oMail.Attachments.Add strDestination & "\" & strReportName & "Page" & i & ".htm", False
  85.     Next
  86. Else
  87.     oMail.Attachments.Add strDestination & "\" & strReportName & ".htm", False
  88.  
  89. End If
  90.  
  91. oMail.Display
  92.  
  93. 'With oMail
  94. '  .To = strRecipient
  95. '  .Body = strBody
  96. '  .Subject = strSubject
  97. '  .Attachments.Add strDestination & "\" & strReportName & ".htm", False
  98. '  .Display
  99. 'End With
  100.  
  101. Set oMail = Nothing
  102. Set oLook = Nothing
  103.  
  104. End Sub
sorry for the sloppiness
Aug 3 '11 #37

P: 86
Is .Attachments add attaching a shortcut or the file directly?

^ problem with MS outlook restricting the ones that are shortcuts.
Aug 3 '11 #38

P: 86
nvm about the previous code.

this is the updated one

Expand|Select|Wrap|Line Numbers
  1. Sub sendEmail()
  2.  
  3. 'Provides the Send Mail automation. Send an E-Mail and Attachment from Access via Outlook
  4. Dim oLook As Object
  5. Dim oMail As Object
  6. Dim olns As Outlook.NameSpace
  7. Dim strRecipient As String
  8. Dim strBody As String
  9. Dim strSubject As String
  10. Dim strReportName As String
  11. Dim strSource As String
  12. Dim strDestination As String
  13. Dim numofFiles As Integer
  14. Dim title As String
  15.  
  16. Set oLook = CreateObject("Outlook.Application")
  17. Set olns = oLook.GetNamespace("MAPI")
  18. Set oMail = oLook.CreateItem(0)
  19.  
  20. '************************** Define your own Values here **************************
  21. strRecipient = ""
  22. strBody = ""
  23. strSubject = "Problem Tracking Ticket #: " & [Forms]![User Problem Log]![trouble_no]
  24. strReportName = Mid("Email-Single", 7) & " Problem Tracking Ticket # " & [Forms]![User Problem Log]![trouble_no]
  25.  
  26. '*********************************************************************************
  27.  
  28. strSource = CurrentProject.Path & "\"
  29. strDestination = "C:\Documents and Settings\jtan\Desktop\Files\"
  30. title = "Single Problem Tracking Ticket # "
  31.  
  32.     If Not FileExists(strDestination) Then
  33.         MkDir (strDestination)
  34.     End If
  35.  
  36.     If FileExists(strDestination & title & [Forms]![User Problem Log]![trouble_no] & "*") Then
  37.         'deletes all files related to that trouble #
  38.         Kill strDestination & title & [Forms]![User Problem Log]![trouble_no] & "*"
  39.         DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatHTML, strDestination & _
  40.                "\" & strReportName & ".htm", False 'create html file page1, page2, etc
  41.     Else
  42.         DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatHTML, strDestination & _
  43.                "\" & strReportName & ".htm", False 'create html file page1, page2, etc
  44.     End If
  45.  
  46.     With oMail
  47.       .To = strRecipient
  48.       .Body = strBody
  49.       .Subject = strSubject
  50.  
  51.       numofFiles = countfiles(strDestination)
  52.  
  53.     If numofFiles > 1 Then
  54.         'adds first page (i.e. Single Problem Tracking Ticket # 9.htm"
  55.         .Attachments.Add strDestination & "\" & strReportName & ".htm", False
  56.         'if more than 2 htm files
  57.         Dim i As Integer
  58.         For i = 2 To numofFiles
  59.             'adds page 2 and on (i.e. Single Problem Tracking Ticket # 9Page2.htm, Page3.htm, etc)
  60.             .Attachments.Add strDestination & "\" & strReportName & "Page" & i & ".htm", False
  61.         Next
  62.     Else
  63.         .Attachments.Add strDestination & "\" & strReportName & ".htm", False
  64.     End If
  65.       .Display 'display ms outlook
  66.     End With
however, MS outlook is still saying it's a shortcut and blocks it. :(
Aug 3 '11 #39

P: 86
and problem is solved. :)

supervisor decided to migrate over to 2007 so PDF is the way to go since there's an acFormatPDF :).
Aug 3 '11 #40

NeoPa
Expert Mod 15k+
P: 31,489
I would say one of the very few real benefits introduced in 2007 was the acFormatPDF option.
Aug 4 '11 #41

P: 86
@NeoPa: I definitely agree...

I don't like the tabs on top and it makes finding things a lot harder...

if MS kept with one UI and just changed some features, everyone would be happy :D

Now I can use this docmd.outputto for my other reports and attach em as PDFs :D.

Thanks everyone who helped me once again!
Aug 4 '11 #42

NeoPa
Expert Mod 15k+
P: 31,489
Jeffrey:
if MS kept with one UI and just changed some features, everyone would be happy :D
So happy in fact, that far fewer would be bothering to keep up with buying all the new releases ;-)
Aug 4 '11 #43

P: 86
@NeoPa or @ADezii

i am having problems sending the email from my windows xp laptop. i installed office 2007 and converted the database to 2007. my colleague tried it on his windows 7 desktop and was able to send the email.

When i open access 2007 and open up the form and click on the email button, i get the following error:

Run-time erro '2282':
The format in which you are attempting to output the current object is not available.

anyone know what this means?

Thanks!
Aug 4 '11 #44

ADezii
Expert 5K+
P: 8,636
  1. Make sure that your Output Format is set to:
    Expand|Select|Wrap|Line Numbers
    1. acFormatPDF
  2. Referring to your prior attempts in Attaching the *.htm Files, try changing all occurrances of .htm in the Code to .html.
Aug 4 '11 #45

P: 86
@ADezii

yeah i changed them all to acFormatPDF, but weird how im still getting an error..

i was thinking it could be a missing reference, but i checked the references my collegue has and it's the same.
Aug 4 '11 #46

NeoPa
Expert Mod 15k+
P: 31,489
If that doesn't enable you to find it by yourself, post the current code and indicate which line triggers the error.
Aug 4 '11 #47

P: 86
Expand|Select|Wrap|Line Numbers
  1. If FileExists(strDestination & title & [Forms]![User Problem Log]![trouble_no] & "*") Then
  2.         Kill strDestination & title & [Forms]![User Problem Log]![trouble_no] & "*"
  3.         DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatPDF, strDestination & _
  4.                 "\" & strReportName & ".pdf", False
  5.      Else
  6.          DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatPDF, strDestination & _
  7.                 "\" & strReportName & ".pdf", False
  8.      End If
line #6-7... when i click on the button to email, it just breaks on that line...

it's weird since i have adobe acrobat installed, pdf writer and all that.
Aug 4 '11 #48

Rabbit
Expert Mod 10K+
P: 12,366
Did you make sure you have the folder "C:\Documents and Settings\jtan\Desktop\Files\" on your laptop?

Also, in your code, you have strDestination & "\". Which means you get "C:\Documents and Settings\jtan\Desktop\Files\\"
Aug 4 '11 #49

P: 86
@Rabbit: there is code above that creates the directory if it does not exist. sorry forgot to add that in post

Expand|Select|Wrap|Line Numbers
  1. If Not FileExists(strDestination) Then
  2.        MkDir (strDestination)
  3.     End If
originally, the strDestination was C:\Windows\Temp\.

I have a feeling it's got something to do with Adobe PDF on my machine.

during the day, i downloaded this template from microsoft called "call tracker" and there was this button that had email formats
http://office.microsoft.com/en-us/te...i:TC010206879|

I clicked on the button, but i do not see Adobe PDF on it.. so that's the thing I'm confused about.
Aug 4 '11 #50

68 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.