Saving an E-mail's text in an Access record, or vice versa.

[spherey]

Hello, all -

I've just built a database in Access, and had a request come up from the end users. I think this is possible, but I'm not sure of the easiest way to go about it. What they want is for the body of chosen emails in MS Outlook to be saved in a record in their Access database. This could happen in one of two ways:
1.) Opening Outlook with a command button on a form in Access, having the record on screen determine automatically who the email recipient would be (and populate the To: field accordingly), and then, once the user has typed an email and sent it, having the text of the email recorded in a field within that same Access record (called 'Notes,' 'Sent E-mails,' or the like),
OR
2.) As in the previous scenario, the user could open Outlook with a command button on a form in Access, and have the record on screen determine automatically who the email recipient would be (and populate the To: field accordingly). The difference would be that the user would've already written out the body of the email in the Access record's 'Notes' field, which would then populate the body of the email automatically.
The difference is that in the first way Outlook populates an Access record, and in the second example, the Access record populates an Outlook email. Either way would be great.
Does anyone have any idea as to how to set up either of these scenarios?

Thanks a lot for any help anyone can offer.

Spherey

 

[seagoing]

The code below is a sample of how to use a command button on an access form to send an email through outlook. As it's a rehash of code I use to broadcast a message to multiple recipients it doesn't exactly solve your problem but the essential elements are there to give you a good idea (I think!)

Of course you'll need a reference in your project to the outlook library and you'll need to validate the content of each data item (Me.msg??????.value's) to ensure only valid emails are sent.

The sample code
Private Sub buttSend_Click()
Dim objOL As Outlook.Application
Dim objNameSpace As Outlook.NameSpace
Dim objMail As MailItem
On Local Error Resume Next
Screen.MousePointer = 11
Set objOL = New Outlook.Application
Set objNameSpace = objOL.GetNamespace("MAPI&quot
Set objMail = objOL.CreateItem(olMailItem)
objMail.Recipients.Add Me.msgAddressTo.Value
objMail.Subject = Me.msgSubject.Value
objMail.Body = Me.msgBody.Value & vbCrLf & vbCrLf & _
"===================" & vbCrLf & _
&quot;from <youremailaddress>&quot; & vbCrLf & vbCrLf & _
&quot;EMail Broadcast&quot; & vbCrLf & _
Format$(Now(), &quot;dd mmm yyyy hh:mm&quot
objMail.Send
Set objMail = Nothing
Set objNameSpace = Nothing
Set objOL = Nothing
Screen.MousePointer = 0
End Sub

 

[spherey]

Thanks for the code!

Could you tell me how I'd place a reference in my project to the outlook library, and how to validate the content of each data item (Me.msg??????.value's) to ensure only valid emails are sent?

Thanks for your help,

Spherey

 

[spherey]

As it is now, when I run that code from the form, I get a compile error. . .

Spherey

 

[JoyInOK]

You can set the reference from the code window. Click Tools/References. Scroll down to Microsoft Outlook and click the box next to it. click OK to set the reference.

If you replace the
objMail.Send
with
objMail.Display
the user will have to manually send the message, which I kinda like because they can also add in any other stuff the recipient might need, but that does not needed to be stored in the database Notes. They can also cancel sending the message.

 

[spherey]

Thank you! I like the idea of having the message displayed before it's sent, too; I changed the line of code to the version you sent, and following your directions, I added a reference to the Outlook library. I'm definitely getting closer, but it's still not working yet.
Now when I click the button, I get this message:
Compile Error: Method or data member not found.
And in the code, this line:

objMail.Recipients.Add Me.msgAddressTo.Value

has this portion highlighted:

.msgAddressTo

I'm still not entirely sure what the code above does; I know it does something very similar to what I need for it to do, but I think in order to make it work for me, I need to be able to customize a few things - first, where it looks in the current on-screen Access record for the email address with which to populate the To: field in the Outlook email (the &quot;Email&quot; field); and next, where it looks in the current on-screen Access record for the text with which to populate the body of the email (the &quot;Status&quot; field).

Thank you both for your help - any clarification and corrections either of you (or anyone else looking over this thread) can offer would be much appreciated.

Thanks,

Spherey

 

[seagoing]

Hi Spherey,

The code I sent you was not an exact solution to your problem, more an example of the logic you would need to implement to provide yourself a solution.

I wasn't expecting you to try and 'compile and go', so to speak.

I see you've managed to set a reference to the outlook library, so that's sorted.

In the example code I posted....
Me.msgAddressTo represents the name of a datafield on the form that contains your command button to send the email. You may well have called this something else!

Validating the data items is really up to you, but very simply it might go something like....

Private Sub buttSend_Click()
Dim objOL As Outlook.Application
Dim objNameSpace As Outlook.NameSpace
Dim objMail As MailItem
Dim bValidFlag as Boolean
bValidFlag = True 'Assume Valid
'***
' Check for valid content (very crude example this)
'***
if trim(Me.msgSubject.Value) = &quot;&quot; _
or trim(Me.msgBody.Value) = &quot;&quot; _
or trim(Me.msgAddressTo.Value) = &quot;&quot; _
or inst(Me.msgAddressTo.Value, &quot;@&quot < 1 then
bValidFlag = False
end if
If bValidFlag = True then
On Local Error Resume Next
Screen.MousePointer = 11
Set objOL = New Outlook.Application
Set objNameSpace = objOL.GetNamespace(&quot;MAPI&quot
Set objMail = objOL.CreateItem(olMailItem)
objMail.Recipients.Add Me.msgAddressTo.Value
objMail.Subject = Me.msgSubject.Value
objMail.Body = Me.msgBody.Value & vbCrLf & vbCrLf & _
&quot;===================&quot; & vbCrLf & _
&quot;from <youremailaddress>&quot; & vbCrLf & vbCrLf & _
&quot;EMail Broadcast&quot; & vbCrLf & _
Format$(Now(), &quot;dd mmm yyyy hh:mm&quot
objMail.Send
Set objMail = Nothing
Set objNameSpace = Nothing
Set objOL = Nothing
Screen.MousePointer = 0
Else
MsgBox &quot;Please ensure valid content for Subject, Body and Recipient&quot;, vbExclamation + vbOKOnly
end if
End Sub

 

[spherey]

Seagoing -

Thanks. And sorry, but I haven't the experience in programming Access to know how to provide myself a solution, even with an example of what sort of logic I'd need to implement. If I tried to 'compile and go,' it was because I assumed that that's what I needed to do.

I suspected that I needed to change parts of the code to reflect the names of the fields on my form, and after your last post, I did so. I'm not sure what else, if anything, I need to change; so I'm afraid I attempted to 'compile and go' again after I had made the changes. And I'm still getting a compile error, which tells me that a sub or function is not defined, and in this line:

Or inst(Me.Email.Value, &quot;@&quot;) < 1 Then

highlights this portion:

inst

Now, perhaps that's to be expected, and there's something else I need to implement. Given the error, I assume so. But being new to the concept of programming such a thing in Access, I don't know what else I need to do or how to go about it.
I noticed that the portion of the code which was causing the trouble was in the bit about validating the data. Ideally I'd like to do that, but as you said it was up to me, and that part of the code was causing the trouble, I tried it without the validation. When I click the button in that case, I don't get the compile error, but nothing seems to happen. I get an hourglass on screen, a minimized Outlook window appears for a split second, then disappears, and nothing else happens. I'm not sure what the button is doing when that happens.

I don't mean to be dense about this; it's simply that I don't have the experience to figure this stuff out, and I'm learning as I go along.

Thanks for your advice and coding,

Spherey

 

[spherey]

It's working now! Hurrah! When I hit the command button, the email is sent to the address specified.

Thank you both very much for all your help,

Spherey

 

[smitan]

Hi Spherey,
your problem might be solved by using 'instr' instead. Instr is a function that searches for a string within a string, in this case searching for '@' in the e-mail address, which is the least one can do before sending an e-mail.

As I use a routine similar to this, you might be aware of the fact that when displaying the message, you are not sure of having a mail sent, though you have a record in your table.

A suggestion is to put a checkbox on your form which gives the user the possibility to send or display. This leads to:
If chkShow then
objMail.Display
else
objMail.Send
end if

Have a nice summer
Hans

 

[spherey]

Thanks for the suggestion!

Spherey