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: [imgnothumb]http://www.everythinga ccess.com/images/SendEmailWarnin g.gif[/imgnothumb]

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!

[ADezii]

Do you wish to automatically Send the E-Mail or Display the Outlook Screen?

[Rabbit]

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.

[Jeffrey Tan]

Do you wish to automatically Send the E-Mail or Display the Outlook Screen?

good question :P

just display the outlook screen before user hits send email.

[Jeffrey Tan]

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.

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

** Snip **

[Jeffrey Tan]

sorry NeoPa.

----

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

="Ticket #: " & [Forms]![User Problem Log]![trouble_no] & " -- " & "Brief Problem: " & [Forms]![User Problem Log]![brief_problem]

i tried vbNewLine, but it doesn't work?

[NeoPa]

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.

[Jeffrey Tan]

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

[NeoPa]

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.

[Jeffrey Tan]

@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

[NeoPa]

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.

[Jeffrey Tan]

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.

[NeoPa]

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.

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

'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
 
Set oLook = CreateObject("Outlook.Application")
Set olns = oLook.GetNamespace("MAPI")
Set oMail = oLook.CreateItem(0)

'************************** Define your own Values here **************************
strRecipient = "JTan@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"
'*********************************************************************************

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

Set oMail = Nothing
Set oLook = Nothing

[Jeffrey Tan]

@ADezii:

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