Need help with code to send Outlook Email from Access

[adubberke]

I've seen several discussions on this topic that I've tried to utilize, but whenever I attempt to initiate the "click" nothing happens. Can someone give me some direction on what I might be doing wrong?

Sub Sendmail()
Dim StrEmail, StrBody As String
Dim objOutlook As Object
Dim objEmail As Object

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(0)

With objEmail
.To = strDistribution_List
.Subject = strTitle
.Body = strTitle
End With
Exit Sub
End Sub

Thank you for any help you can provide!

 

[Andrzejek]

From here:

Add a reference to the Outlook object model in the Visual Basic editor. Then you can use the code below to send an email using outlook.

Dim oApp As Outlook.Application
Dim oMail As MailItem
Set oApp = CreateObject("Outlook.application")

Set oMail = oApp.CreateItem(olMailItem)
With oMail
    .Body = "Body of the email"
    .Subject = "Test Subject"
    .To = "Someone@somewhere.com"
    .Send
End With
Set oMail = Nothing
Set oApp = Nothing

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Andrzejek]

Without the reference to Outlook (late binding), try this:

Dim oApp As Object
Dim oMail As Object
Set oApp = CreateObject("Outlook.application")

Set oMail = oApp.CreateItem(0)
With oMail
    .Body = "Body of the email"
    .Subject = "Test Subject"
    .To = "Someone@somewhere.com"
    .Send
End With
Set oMail = Nothing
Set oApp = Nothing

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[adubberke]

Hi Andy,

First, thanks for your help. Second, is there a reason why, when I click the command button on my form, nothing happens? Shouldn't an Outlook mail form pop up?

Thanks!

Aubrey

 

[Blorf]

What is the coding behind your command button? If this code were within your on click event, the sub name would be something like MyButton_Clock, not Sub Sendmail().

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That DonÆt.

http://www.thelegacyreturns.com/

 

[Andrzejek]

adubberke,
Are you trying to:
1. Create an e-mail in Outlook and show it to the user without actually sending it to the recipient? Or
2. Just send an e-mail from Access thru Outlook without showing to the user the actual e-mail?


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[adubberke]

I'm trying to create an email in outlook and show it to the user without sending it to the recipient.

 

[Andrzejek]

Add a reference to the Outlook object model in the Visual Basic editor.

Option Explicit

Private Sub Command1_Click()

Dim oApp As New Outlook.Application
Dim oMail As MailItem

Set oMail = oApp.CreateItem(olMailItem)

With oMail
    .Body = "Body of the email"
    .Subject = "Test Subject"
    .To = "Someone@somewhere.com"[green]
    '.Send[/green]
    .Display
End With

Set oMail = Nothing
Set oApp = Nothing

End Sub

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[adubberke]

IT WORKED!!! Thank you, thank you, thank you!

Ok, one last question...

I want the body of the email to reference a specific field. However, when it does, I get this:

<div>XXX</div>

If I don't want the "<div>" and the "</div>", how do I refer the field in the code to only get the text "XXX"?

(If you can't tell, I'm quite the novice when it comes to this stuff)

 

[Andrzejek]

Well, it would help if I could see your code.
Make sure you use TGML tags (above the window where you type your posts) to display code like in my posts.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[adubberke]

Sure. I'm pretty much using what you sent, but replaced the .Body, .Subject,and .To portions.

Private Sub SendEmail_Click()

Dim oApp As New Outlook.Application
Dim oMail As MailItem

Set oMail = oApp.CreateItem(olMailItem)

With oMail
    .Body = IIf(IsNull(Description), "", Description)
    .Subject = Title
    .To = Distribution_List
    '.Send
    .Display
End With

Set oMail = Nothing
Set oApp = Nothing

End Sub

 

[Andrzejek]

That does not really help me. :-(

First of all, do you have [tt]Option Explicit[/tt] at the top of your code? If not, you should have it.
Second, I would need to see the whole code of yours, not just this small part, because I have no idea how Description is defined and where is it coming from


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[adubberke]

Well, I don't know what happened, but those have gone away. And this really is my whole code. I have this tied to a command button on a form to execute on click. The "description" is a field on the form.

Really appreciate all of your help!

 

[Andrzejek]

Great!

And welcome to Tek-Tips

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.