Send email of selected record from form

[a10scourt]

Hello all,

I have a searchable database that is set up in a similar fashion as Allen Browne's example found here. In the "search results" I then have a button that allows the user to view each form in a single view, using the following code:

Private Sub Command151_Click()
    DoCmd.OpenForm "ArticleEdit", , , "ArticleID = " & Me.ArticleID
End Sub

This grabs the exact record that the user wants, as returned by the search results and displays it in a new form, where they can edit the information. I am trying to figure out a way to have a button that emails the user the information found in that record (which includes a .pdf attachment).

I have everything working except getting the record information (the most important part) in the body of the email. Here is my code thus far:

Private Sub Command173_Click()
    Dim olapp As Outlook.Application
    Dim olNewMail As Outlook.MailItem
    Dim eTo, eSubject, ebody As String
    eTo = ""
    eSubject = "Article Please Read"
    ebody = "Record Information" 'I need to place the record here
    Set olapp = New Outlook.Application
    Set olNewMail = olapp.CreateItem(olMailItem)
    olNewMail.To = eTo
    olNewMail.Subject = eSubject
    olNewMail.Body = ebody
    olNewMail.Display
End Sub

It creates the email just fine, but I have no idea how to get the record info in the body. Any guidance would be greatly appreciated

 

[pbaldy]

If the data is on the form, you can simply do this type of thing:

olNewMail.To = Me.EmailAddress

 

[a10scourt]

pbaldy, thanks for your reply! I see what you are saying, perhaps you could provide some additional guidance. There are 6-7 fields that I want to send in the body of the email. How would I go about having all of these included in the email. For example: I want to send Title, AuthorLN, AuthorFN, and a .pdf of the article. Using your example I would do:

ol.NewMail.Body = Me.Title

To include the title field value of my selected record in the body of the email.
But I am not sure how to add the other items as well (I am still new to VBA)

 

[a10scourt]

Alright I got everything to work, just by adding & (duh ) except for the pdf attachment.

 

[pbaldy]

Glad you got it working. In case you didn't know, you can concatenate

vbCrLf

into the string for a carriage return:

"This will be on one line" & vbCrLf & "This will be on the next line"

 

[a10scourt]

Well, I almost have it working, I am still struggling with the attachment. This is my coding:

Private Sub Command174_Click()

    Dim olapp As Outlook.Application
    Dim olNewMail As Outlook.MailItem
    Dim eTo, eSubject, eBody As String
    eTo = ""
    eSubject = "Article Please Read"
    Set olapp = New Outlook.Application
    Set olNewMail = olapp.CreateItem(olMailItem)
    olNewMail.To = eTo
    olNewMail.Subject = eSubject
    olNewMail.Body = Me.Title & vbCrLF & Me.AuthorLN & ", " & Me.AuthorFN
    olNewMail.Attachments = Me.Article
    olNewMail.Display
    
End Sub

When I run it, I get a "Property is read only" erorr message... any thoughts?

Oh and thanks for the vbCrLF hint

 

[pbaldy]

This has the proper syntax to add an attachment (you can still use your form reference):

http://support.microsoft.com/?kbid=161088

 

[a10scourt]

Thanks for the info, so how would I have this module run on a "on-click" event?

 

[pbaldy]

You don't have to use that module, the relevant part you want to incorporate into your existing code is this:

Set objOutlookAttach = .Attachments.Add(AttachmentPath)

You'd also want the declaration of that variable at the top.

 

[a10scourt]

I have been mulling over this now since yesterday and I just can't seem to get it right heres the latest:

Private Sub Command175_Click()
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim objAttach As Outlook.Attachments
Set olApp = Outlook.Application
'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)
objMail.To = ";"
objMail.Subject = "Please Read this article"
objMail.Body = Me.Title
objAttach.Add (Me.Article)
objMail.Display

End Sub

I still get this error: "Run-time error '483' Object doesn't support this property or method"

And ideas?

I've even tried replicating what they have and I couldn't get it to work.

 

[pbaldy]

Let me put the line from the link and yours together:

Set objOutlookAttach = .Attachments.Add(AttachmentPath)
objAttach.Add (Me.Article)

Yours is not at all similar. Also their declaration and yours:

Dim objOutlookAttach As Outlook.Attachment
Dim objAttach As Outlook.Attachments

Fix that and try:

Set objAttach = .Attachments.Add(Me.Article)

 

[a10scourt]

Thanks for the clarification. When I run the code, I get a "compile erorr: Invalid or unqualified reference." It then highlights the .Attachments in the code line Set objAttach = .Attachments.Add(Me.Article)

 

[pbaldy]

Sorry, I missed the fact that the sample had a "With" going. Try this:

Set objAttach = objMail.Attachments.Add(Me.Article)

 

[a10scourt]

I get "Error 438: Object doesn't support this property or method"

 Private Sub SendEmail_Click()

    Dim olApp As Outlook.Application
    Dim objMail As Outlook.MailItem
    Dim objAttach As Outlook.attachment

    Set olApp = Outlook.Application
    Set objMail = olApp.CreateItem(olMailItem)

    objMail.To = ";"
    objMail.Subject = "Please Read this article"
    objMail.Body = Me.Title
    Set objAttach = objMail.Attachments.Add(Me.Article)
    objMail.Display

End Sub

Oh and by the way thanks so much for you help on this, is there a way to give you point or upvote or something of that nature?

 

[pbaldy]

Don't thank me yet; I haven't solved it yet!

I assume the error is on the attachment line? I just dug up a working procedure of mine which looks like this:

...Attachments.Add "c:\DealerRO.pdf"

Which I note is a little different than that site and doesn't have the parentheses. Try it without, like this (I'm assuming your form reference contains a path?):

objMail.Attachments.Add Me.Article

If that doesn't work, can you post the db? It would help to be able to play with it.

 

[a10scourt]

So I tried that and couldn't get that to work. It works just fine if I type in a file path, similar to your example, but I can't get it to pull the article from that specific record. So below are a slim version of my db, front and back end. Maybe letting you play with it will get something going