469,268 Members | 970 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,268 developers. It's quick & easy.

send email without using a macro

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
68 7078
NeoPa
32,171 Expert Mod 16PB
Jeffrey, did you miss Rabbit's point. The problem is that you have doubled up on the "\" characters. He's correct. That is your problem.
Aug 5 '11 #51
@NeoPa:

oh. I'll try that when i get to work today. Maybe that is the problem. Thanks!

Hopefully it is so this can be closed once and for all :P
Aug 5 '11 #52
NeoPa
32,171 Expert Mod 16PB
There's no question about it Jeffrey. Even if there are any other problems, that is certainly going to cause your issue all by itself. Your code clearly adds an extra "\" in a few places, which will cause file references to fail.
Aug 5 '11 #53
@NeoPa:

It just puzzles me because the code I sent to my collegue is the exact one i have and his worked. but i'll be sure to double check every line of code when i go to the office.
Aug 5 '11 #54
NeoPa
32,171 Expert Mod 16PB
I understand Jeffrey.

We can only work with what's in front of us though. I doubt the code you sent to your colleague is exactly as you posted for us (otherwise it wouldn't work), but that isn't a puzzle we need to worry about just now. If that doesn't resolve itself in the wash then we can look at that too, but we'd need a new question and the code your colleague is actually using (rather than what you think you sent to him at some point - however close a match that may or may not turn out to be).

For now though, I'm confident the code you posted has that problem in it (even if there may be others too).
Aug 5 '11 #55
@NeoPa:

yep. what i will do is copy the exact code from my colleague's database, put it into mine and test it.

in terms of programs and all, what are usually the requirements to get AcFormatPDF to work?

Microsoft Access 2007
Adobe Acrobat

do you know of anything else?

trying to open up some possibilities and narrow them down when i troubleshoot this. :)
Aug 5 '11 #56
NeoPa
32,171 Expert Mod 16PB
Jeffrey:
yep. what i will do is copy the exact code from my colleague's database, put it into mine and test it.
I hope you're referring to what you will do after we finish here. That is not something for this thread. Nor is the question about how to work with PDFs.

For now, you need to fix the code you posted earlier, exactly as suggested by Rabbit, and report on your success.

Anything further can find its way into a new thread if required.
Aug 5 '11 #57
understood :)

thanks again for all this help.
Aug 5 '11 #58
ADezii
8,800 Expert 8TB
@NeoPa & Rabbit:
Ironically, the addition of an extra '\' in the OutputFile Argument of the OutputTo() Method, between the lowest level Folder and start of the Filename, will work. The following Code will work with no problems (notice addition of extra '\'):
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acOutputTable, "tblEmployees", acFormatRTF, "C:\Windows\\Employees.rtf", False
Aug 5 '11 #59
ok im at work now and i fixed the additional "\" but still the same problem.

here's the code:

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 title As String
  14.  
  15. Set oLook = CreateObject("Outlook.Application")
  16. Set olns = oLook.GetNamespace("MAPI")
  17. Set oMail = oLook.CreateItem(0)
  18.  
  19. '************************** Define your own Values here **************************
  20.  
  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:\Windows\Temp\" 'give destination
  30. title = "Single Problem Tracking Ticket # "
  31.  
  32.     If Not FileExists(strDestination) Then
  33.         MkDir (strDestination)
  34.     End If
  35.     If FileExists(strDestination & title & [Forms]![User Problem Log]![trouble_no] & ".pdf") Then
  36.         Kill strDestination & title & [Forms]![User Problem Log]![trouble_no] & ".pdf"
  37.         DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatPDF, strDestination & strReportName & ".pdf", False
  38.     Else
  39.         DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatPDF, strDestination & strReportName & ".pdf", False
  40.     End If
  41.     With oMail
  42.       .To = strRecipient
  43.       .Body = strBody
  44.       .Subject = strSubject
  45.       .Attachments.Add strDestination & strReportName & ".pdf", False
  46.       .Display
  47.     End With
  48. End Sub
Aug 5 '11 #60
NeoPa
32,171 Expert Mod 16PB
@ADezii
Thanks for the clarification. I should apologise for misleading. I always use Replace() myself to ensure such an anomaly never arises.

@Jeffrey
I'm sure the problem is still the same. Would you mind posting the details again as I don't keep details of all the threads I work on in my RAM, and aging as I am, my HDD is not too hot either. Short of that I would need to search through the thread again, and it's a long thread already.
Aug 5 '11 #61
ah i noticed something else...

see the attached screenshot..



there's no PDF format.

i looked at my colleague's computer and there is PDF... now i'm 100% sure that is the problem.
Aug 5 '11 #62
NeoPa
32,171 Expert Mod 16PB
Did you not say you were developing on a 2007 system? If not, then acOutputPDF was introduced in 2007 so that would explain the omission. If so, then I can only guess that support for PDF is an optional inclusion and you may need to reinstall the software making sure that PDF support is included.
Aug 6 '11 #63
@NeoPa:

Yeah the database was recently converted from 2000 (or 2002, i forget) to 2007. doubt it has anything to do with the fact that he's using windows 7 and i'm still on XP. maybe ill try to reinstall access 2007 and adobe pdf.
Aug 6 '11 #64
omg finally i fixed it!

i reinstalled MS office and adobe acrobat... still the same problem.. so i figured i'd google it and see if anything came up.

Turns out that I was missing an addin for 2007.

http://support.microsoft.com/kb/934833

so i downloaded the Microsoft Save as PDF or XPS addin, installed it and now it works perfectly :)
Aug 8 '11 #65
NeoPa
32,171 Expert Mod 16PB
So not only is 2007 required, but a separate download is also required. Sorry for the bum info Jeffrey. I had no idea that PDF wasn't available as standard in 2007 (I still only use up to 2003 personally). Well done for clearing that up though
Aug 8 '11 #66
@NeoPa: yeah i didn't know either lol.

So I guess this would be a good reference for those who want to try the same and gets to this problem as well :)
Aug 8 '11 #67
NeoPa
32,171 Expert Mod 16PB
I guess so. It can't hurt :-)
Aug 8 '11 #68
anoble1
235 100+
In my database I do something very similar. Works GREAT!
Something like this:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acOutputReport, "rptCrewAuthorization", acFormatSNP, outFileName(0), False
Expand|Select|Wrap|Line Numbers
  1. GenerateEmail "asdf@asdf.com", "sdfg@sdfg.com;dfgh@dfgh.com", "fghj@fghj.com;ghjk@ghjk.com", ContractorName.Value & " Crew Authorization " & cmbContractID.Column(1, cmbContractID.ListIndex) & "-" & AuthNum.Value & " - Starting " & Forms!frmCrewAuthorization!AuthDate.Value, Forms!frmCrewAuthorization!Name.Value & "'s Crew" & vbCrLf & vbCrLf, outFileName, False
Aug 10 '11 #69

Post your reply

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

Similar topics

6 posts views Thread by Mike | last post: by
4 posts views Thread by CLEAR-RCIC | last post: by
2 posts views Thread by Boki | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.