send email without using a macro

[Jeffrey Tan]

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!

[NeoPa]

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.

[Jeffrey Tan]

@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

[NeoPa]

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.

[Jeffrey Tan]

@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.

[NeoPa]

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).

[Jeffrey Tan]

@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. :)

[NeoPa]

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.

[Jeffrey Tan]

understood :)

thanks again for all this help.

[ADezii]

@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 '\'):

DoCmd.OutputTo acOutputTable, "tblEmployees", acFormatRTF, "C:\Windows\\Employees.rtf", False

[Jeffrey Tan]

ok im at work now and i fixed the additional "\" but still the same problem.

here's the code:

Sub sendEmail()
 
'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 strSource As String
Dim strDestination As String
Dim title As String
 
Set oLook = CreateObject("Outlook.Application")
Set olns = oLook.GetNamespace("MAPI")
Set oMail = oLook.CreateItem(0)
 
'************************** Define your own Values here **************************
 
strRecipient = ""
strBody = ""
strSubject = "Problem Tracking Ticket #: " & [Forms]![User Problem Log]![trouble_no]
strReportName = Mid("Email-Single", 7) & " Problem Tracking Ticket # " & [Forms]![User Problem Log]![trouble_no]
 
'*********************************************************************************
 
strSource = CurrentProject.Path & "\"
strDestination = "C:\Windows\Temp\" 'give destination
title = "Single Problem Tracking Ticket # "
 
    If Not FileExists(strDestination) Then
        MkDir (strDestination)
    End If
    If FileExists(strDestination & title & [Forms]![User Problem Log]![trouble_no] & ".pdf") Then
        Kill strDestination & title & [Forms]![User Problem Log]![trouble_no] & ".pdf"
        DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatPDF, strDestination & strReportName & ".pdf", False
    Else
        DoCmd.OutputTo acOutputReport, "Email-" & Mid(strReportName, 1, 6), acFormatPDF, strDestination & strReportName & ".pdf", False
    End If
    With oMail
      .To = strRecipient
      .Body = strBody
      .Subject = strSubject
      .Attachments.Add strDestination & strReportName & ".pdf", False
      .Display
    End With
End Sub

[NeoPa]

@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.

[Jeffrey Tan]

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.

[NeoPa]

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.

[Jeffrey Tan]

@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.

[Jeffrey Tan]

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 :)

[NeoPa]

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

[Jeffrey Tan]

@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 :)

[NeoPa]

I guess so. It can't hurt :-)

[anoble1]

In my database I do something very similar. Works GREAT!
Something like this:

DoCmd.OutputTo acOutputReport, "rptCrewAuthorization", acFormatSNP, outFileName(0), False

GenerateEmail "[e-mail]", "[e-mail];[e-mail]", "[e-mail];[e-mail]", 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