Open New Outlook email with Command button

[Dzine2009]

Ok all fixed. Still getting the same error message with the , highlighted in the string

Chr(34)), " ")

so the full effect is

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 " & NV(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

 

[boblarson]

You have NV there:

NV(DLookup("SiteAddress", "Inspection", "[SiteAddress]=" & Chr(34) & Me!Site_Address) & Chr(34)), " ")
.Display

Needs to be NZ and the quotes need to be no space - ""

NZ(DLookup("SiteAddress", "Inspection", "[SiteAddress]=" & Chr(34) & Me!Site_Address) & Chr(34)), "")
.Display

 

[Dzine2009]

fixed but same error same higlight. Bob I am so sorry...

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

 

[boblarson]

I can't see anything else, other than you have two .Body in there which won't work. You should have a variable which you can then assign at the end of the building of the body.

Can you post the database?

 

[Dzine2009]

It doesnt want to let me attach them. I could give you access to an ftp.

 

[boblarson]

It doesnt want to let me attach them. I could give you access to an ftp.

Make sure you

1. run Compact and Repair first

2. Zip it (if you don't have something like WinZip, you can just right-click on the file and select SEND TO > COMPRESSED FOLDER which is built in to WinXP, WinVista, and Win7.

 

[pbaldy]

NZ(DLookup("SiteAddress", "Inspection", "[SiteAddress]=" & Chr(34) & Me!Site_Address) & Chr(34)), "")

 

[boblarson]

Never mind - I'm an idiot - I was thinking you were saying to PUT one there.

 

[Dzine2009]

Ok got rid of that nasty little guy and removed the extra .body but of course I am screwing this up tons and it doesnt work. I assume I need to put something inbetween the 2 calls but what?

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 = "slove19_2000@yahoo.com"
    .Subject = "Your inspection has been scheduled."
    .Body = " Your inspection has been scheduled for " & Format(DLookup("InspectionDate", "Inspection", "[InspectionID]=" & Me!InspectionID), "mmmm d, yyyy")
    " at the following address " & NZ(DLookup("SiteAddress", "Inspection", "[SiteAddress]=" & Chr(34) & Me!SiteAddress & 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

So basically I want my email to read...

Your inspection has been scheduled for (insert date) for the following site (insert site address).

I get the following error

Compile Error: Expected End of Statement and the " at the following address " is highlighted.

Do you still want me to upload the database

 

[boblarson]

.Body = " Your inspection has been scheduled for " & Format(DLookup("InspectionDate", "Inspection", "[InspectionID]=" & Me!InspectionID), "mmmm d, yyyy") &
" at the following address " & NZ(DLookup("SiteAddress", "Inspection", "[SiteAddress]=" & Chr(34) & Me!SiteAddress & Chr(34)), "")

 

[Dzine2009]

Duh Duh Duh.... I should have seen that comming. Could you recommend a good book for a beginner in VB. I'd love to be able to do more of this with out having to bug the heck out of you all.

 

[boblarson]

Duh Duh Duh.... I should have seen that comming. Could you recommend a good book for a beginner in VB. I'd love to be able to do more of this with out having to bug the heck out of you all.

Here are a couple of books:

Beginning Access 2007 VBA

Access 2007 VBA Programmers Reference

And a good set of tutorials (Free):

 

[Dzine2009]

Thanks to all of you! I'll start reading up asap but probably not soon enough to finish this project so Im sure you will see me around again. As for today my brain is wracked.

Kudos to all of you, your help is more than appreciated!

 

[pbaldy]

spammer reported