Open New Outlook email with Command button

D

Dzine2009

Registered User.
Local time
Today, 10:15
Joined
Aug 17, 2009
Messages
27
Access 2007
Outlook 2007

I am totally new to VB and Access. I would like to create a command button that would open a new Outlook email with the Subject and body already filled in. I tried the wizard already but to no avail. Could someone please help me with this. Eventually I want to be able fill in the "To" line with addresses from a specific record in the database but for now I just want Access to open a new email in outlook. Obviously Im totally new to this so step by step would be helpful.

Thanks
 
If you go into the VBA editor and search for SendObject. I dont know if the useage has changed since 2003, but that is the command you are looking for.
 
Ok so I've been searching for a sample script and found one that looks like it works well but when I run it I get the following error message

Compile error: method or data member not found.

Code: 
Private Sub Command22_Click()
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
    .BodyFormat = olFormatRichText
    .To = Me.Email_Address
    .Subject = Me.Mess_Subject
    .HTMLBody = Me.Mess_Text
    If Left(Me.Mail_Attachment_Path, 1) <> "<" Then
        .Attachments.Add (Me.Mail_Attachment_Path)
    End If
    '.DeleteAfterSubmit = True 'This would let Outlook send the note without storing
     it in your sent bin
    .Display
End With
'MsgBox MailOutLook.Body
Exit Sub
email_error:
MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
Resume Error_out
Error_out:
End Sub

I assume the code is trying to pull an email address from somewhere. I would like the .to feild to be to a specific email address but I am unsure how the syntax should read.
 
Dzine2009 said:
I assume the code is trying to pull an email address from somewhere. I would like the .to feild to be to a specific email address but I am unsure how the syntax should read.
Click to expand...
That would be replace the part in red:

.To = Me.Email_Address

Like

.To = "something@xyz.com"
 
Duh sorry I knew that just didnt click you know...

Now I would like to be able to insert information from my table into the email. Could someone point me to some sort of a guide.
 
It occured to me that I may need to be more specific.

The following is the code that I am using..

Code: 
Private Sub Command20_Click()
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
.To = "[EMAIL="slove19_2000@yahoo.com"]slove19_2000@yahoo.com[/EMAIL]"
.Subject = "Your inspection has been scheduled."
.Body = " Your inspection has been scheduled for August 1st 2009. "
.Display
End With
'MsgBox MailOutLook.Body
Exit Sub
email_error:
MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
Resume Error_out
Error_out:
End Sub

Instead of having to type in the date I would like it to pull the date from a table in the database.
 
You could do something like:
Code: 
.Body = " Your inspection has been scheduled for " & Format(DLookup("DateFieldNameInTable", "TableNameHere", "[IDField]=" & Me!YourIDFieldOnForm),"mmmm d, yyyy")
 
So like this. I feel like Im taking advantage of you bob...

Code: 
Private Sub Command20_Click()
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
    .BodyFormat = olFormatRichText
    .To = "[EMAIL="slove19_2000@yahoo.com"]slove19_2000@yahoo.com[/EMAIL]"
    .Subject = "Your inspection has been scheduled."
    .Body = " Your inspection has been scheduled for " & Format(DLookup("InspectionDate", "Inspection", "[IDField]=" & Me!InspectionDate),"mmmm d, yyyy")
    .Display
End With
'MsgBox MailOutLook.Body
Exit Sub
email_error:
MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
Resume Error_out
Error_out:
End Sub
 
Not quite - If you already have the date (Me!InspectionDate) then you wouldn't need to look it up in the table now would you? You would need the ID of the record that you want the date for. I assume (perhaps incorrectly) that you are storing the client's ID along with the date that they need to come back. So, you would look up the date based on the client's ID or the primary key of the record.
 
GOT IT!!! Bob you are amazing. I dont know what I would have done without you! I can start to see how the code is working now and how the syntax must be built. I will probably have more questions but I really appreciate your help.
 
Crap back already! Ok I want to call the site address after some more text.


So I thought this would work. I hate being a noob...

Code: 
Private Sub Command20_Click()
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
    .BodyFormat = olFormatRichText
    .To = "[EMAIL="slove19_2000@yahoo.com"]slove19_2000@yahoo.com[/EMAIL]"
    .Subject = "Your inspection has been scheduled."
    .Body = " Your inspection has been scheduled for " & Format(DLookup("InspectionDate", "Inspection", "[InspectionID]=" & Me!InspectionID), "mmmm d, yyyy")
  .Body = "at the following address..." & Format(DLookup("SiteAddress", "Inspection:, "[SiteAddress]=" & Me!SiteAddress)
    .Display
End With
'MsgBox MailOutLook.Body
Exit Sub
email_error:
MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
Resume Error_out
Error_out:
End Sub

But I get an error message...

Compile Error: Expected list seperator or ) Must have gotten something wrong...
 
Remove the

Format(

part in front of your DLookup on that one.
 
And you need a double quote:

"Inspection:,

"Inspection:",
 
Ok fixed thoes and now I get

Compile Error: Expected end of statement It highlights DLookup

Bob I cant thank you enough... I really cant
 
Dzine2009 said:
Ok fixed thoes and now I get

Compile Error: Expected end of statement It highlights DLookup

Bob I cant thank you enough... I really cant
Click to expand...

Go ahead and post what you've currently got (since we have done some fixes since the last one I'm not sure exactly without seeing it).
 
Code: 
Private Sub Command20_Click()
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
    .BodyFormat = olFormatRichText
    .To = "[EMAIL="slove19_2000@yahoo.com"]slove19_2000@yahoo.com[/EMAIL]"
    .Subject = "Your inspection has been scheduled."
    .Body = " Your inspection has been scheduled for " & Format(DLookup("InspectionDate", "Inspection", "[InspectionID]=" & Me!InspectionID), "mmmm d, yyyy")
    .Body = "at the following address..." DLookup("SiteAddress", "Inspection", "[SiteAddress]=" & Me!SiteAddress)
    .Display
End With
'MsgBox MailOutLook.Body
Exit Sub
email_error:
MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
Resume Error_out
Error_out:
End Sub
 
Okay, a couple of things.

Again, you are looking up SiteAddress but referring to Me!SiteAddress. So, if you have the SiteAddress with Me!SiteAddress then you would not need to look up the address.

Also, if it is text then you would need quotes (and perhaps an NZ function in case it returned a null):

Nz(DLookup("SiteAddress", "Inspection", "[SiteAddress]=" & Chr(34) & Me!SiteAddress) & Chr(34)), "")
 
Ok fixed thoes.

Now I have this and still get the same error message with the Nz highlighted.

Code: 
Private Sub Command20_Click()
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
    .BodyFormat = olFormatRichText
    .To = "[EMAIL="slove19_2000@yahoo.com"]slove19_2000@yahoo.com[/EMAIL]"
    .Subject = "Your inspection has been scheduled."
    .Body = " Your inspection has been scheduled for " & Format(DLookup("InspectionDate", "Inspection", "[InspectionID]=" & Me!InspectionID), "mmmm d, yyyy")
    .Body = " at the following address" Nz(DLookup("SiteAddress", "Inspection", "[SiteAddress]=" & Chr(34) & Me!Site_Address) & Chr(34)), "")
    .Display
End With
'MsgBox MailOutLook.Body
Exit Sub
email_error:
MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
Resume Error_out
Error_out:
End Sub
 
You need an ampersand right after this part:

" at the following address"

so

" at the following address" & Nz(DLookup("SiteAddress", "Inspection", "[SiteAddress]=" & Chr(34) & Me!Site_Address) & Chr(34)), "")


Also, you will need a space after the word address

" at the following address " & Nz(DLookup("SiteAddress", "Inspection", "[SiteAddress]=" & Chr(34) & Me!Site_Address) & Chr(34)), "")
 
You must log in or register to reply here.

Similar threads

Jason Lee Hayes
Solved Open Outlook & check for new mail where search criteria is applied to the email subject line
Replies
19
Views
656
Jason Lee Hayes
Jason Lee Hayes
S
How to Open Outlook Classic from website email address?
Replies
15
Views
653
Gasman
Gasman
gemma-the-husky
New Outlook
2
Replies
22
Views
1,328
The_Doc_Man
The_Doc_Man
T
Access When emailing brings up Outlook 2016 wizard to add email account
Replies
10
Views
440
The_Doc_Man
The_Doc_Man
E
Emmanuel Katto : How to Set Outlook to Open in HTML Format from Access Email
Replies
2
Views
182
Trevor G
Trevor G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom