Automatically sending an e-mail from access 2

[annmariebette]

Help! If anyone out there can give me an idea of how to do this I would GREATLY appreciate it! I have a form in Access (2000) that has a button on it. When clicked I execute a Visual Basic event that basically prints a single record from the form in a report (preview mode). The VB code next runs a macro that basically executes the SendTo action which Opens Outlook and transmits the report as an attachment. What I need to do is figure out a way of getting the email address (which I know from both the form and report) and automatically have it populated to "send" to the email recepient. Here's what my event code looks like that's attached to the button on my form:

Private Sub cmdPrintRecord_Click()
Dim strReportName As String
Dim strCriteria As String

strReportName = "rptInstructorTEST"
strCriteria = "[InstructorID]=" & Me![InstructorId] & ""
DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

Dim strDocName As String
Dim strEmail As String

strDocName = "mcrTransmitToOutlook"
strEmail = ""
DoCmd.RunMacro strDocName
End Sub

The macro TransmitToOutlook" basically launches outlook and as I mentioned above attaches the report as an email attachment. What I want to do is take the email address and have it automatically populated in the TO: field in Outlook and if possible send the email.

I am DESPARATE!
THanks so much,
Ann Marie

 

[Hap007]

Dear Ann Marie,

Try this FUnction:

Public Function SendEmail(MailTo As String, CCTo As String, BCCTo As String, Subject As String, Message As String, Optional EditBeforeSending As Boolean = False, Optional Attachment As String) As Boolean
Const olByValue = 1
Dim OL As Object
Dim MyMailItem As Object
Dim AName As String
Dim A As Long
'Dim OL As Outlook.Application
'Dim MyMailItem As Outlook.MailItem

Const olMailItem = 0

Set OL = CreateObject("Outlook.Application&quot
Set MyMailItem = OL.CreateItem(olMailItem)
With MyMailItem
.To = MailTo
.CC = CCTo
.BCC = BCCTo
.Subject = Subject
If Attachment <> &quot;&quot; Then
If Dir(Attachment) <> &quot;&quot; Then
'Display it without the '.lnk'
AName = Attachment
A = InStr(1, AName, &quot;\&quot
While A <> 0
AName = Mid$(AName, A + 1)
A = InStr(1, AName, &quot;\&quot
Wend
A = InStr(1, AName, &quot;.&quot
If A > 0 Then
AName = Left$(AName, A - 1)
End If
If AName = &quot;&quot; Then AName = Attachment 'Make sure we have something at least
.Attachments.Add Attachment, olByValue, 1, AName ' Add A Copy (Not Shortcut)
End If
.body = vbCr & Message
Else
.body = Message
End If
If EditBeforeSending Then
.Display
Else
On Error Resume Next
.Send
If Err.Number <> 0 Then
MsgBox &quot;Email not sent!&quot;, vbExclamation
Err.Clear
End If
On Error GoTo 0
End If
End With

Set MyMailItem = Nothing
Set OL = Nothing
End Function

Hope This Helps,
Hap


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[annmariebette]

Hap,
THanks so much for such a fast reply. I'm a little confused. Where do I execute or attach the public function to? How do I attach it to my code? I am a VERY new Visual Basic person (trust me that's no lie!!)
Thanks,
ANn Marie

 

[Hap007]

Dear Ann Marie,

Public Functions are normally placed in a 'Module'
Just create a new module in your access mdb, then copy the example here and paste into the code window of the new module.

FYI:
'Public' Functions can be seen by all forms, reports, other modules.

'Private' Funtions can only be seen by the form or module where the function resides.

You normally use public when the code will be used by more than one form or the like.

You could make the sample I provided 'Private' and included it in you code of the form. What ever makes you happy, as long as it works.

Good Luck,
Hap

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[annmariebette]

Hap,
I am still confused! I created a private sub that is working great. I have a button on a form that when clicked submits a report for that persons record and transmits it as an email attachment, automatically sending it to the email address populated in a field I have. That was easy because I'm working with one record at a time. However....

Now my user wants me to make a change to her database so that she can run a report and electronically transmit an email for every record produced by this report! If I have 233 records generated by this report, how do I transmit 233 emails? Attaching the individual record info to the respective email? This sounds too nasty!!!Is there a way of doing this from the table or query itself? Is there something I could attach your public sub too? What property? Should I use a report??

SOS!!
Ann Marie

 

[Hap007]

Dear Ann Marie,

You would probably want to do this in VBA Code.
You would create a Recordset from your table or query.
Then, you would read through all the records, sending each record an email, then reading the next record and looping until the End of File EOF was reached.

Hope This Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[annmariebette]

Hap,
Thanks for your reply. My problem is I am not a VBA coder. Maybe I should hang this one up??!!
I can do some basic things in Visual Basic but I have never really written anything extensively. Actually, I guess I would be reading a recordset from a query. Her report and form are based on data from a query. Could you give me some example code?

BTW, I submitted a post for you as an expert help! I don't know who you are or where you work but you are definitely a VBA guru.

Appreciate your help,
Ann Marie

 

[Anomaly]

Try creating the email function in Macro Builder (Send Object) and binding it to the command button, under properties, event tab, specific event. There is an option in the Send Object Macro to populate subjects, email addresses, etc... Once the Macro has been run from the form Access it may even convert it into an event procedure automatically.

 

[egobbitz]

Create a report off of the query, and then use the DoCMD.sendObject to send the report via email to the recipient. I don't think you would want to send 233 emails at one time.

 

[Irish1957]

I hope th program below may help you with your problem. I have used it in the past in several different forms to send bulk emails. It works with Access97 & 2000 and Outlook 97, 98 and 2000.

The program below works to send bulk emails with or without attachments. It needs to have your current outlook library clicked as a reference Tools>Reference.

Dim DBdb1 As Database ' Database containing all the emails addresses to be sent to
Dim rst As Recordset
Dim strEmail As String
Dim FirstName As String ' Persons First name extracted from data tale
Dim Surname As String
Dim attachment As String
Dim mev1 As Variant
Dim mev2 As Variant
Dim mev3 As String

Dim objOutlook As New Outlook.Application
Dim objRem As MailItem
Dim flag As Boolean
On Error Resume Next
' read send list name from form This allows you to use several different send lists
mev3 = Me![Text10]

' indicates sending emails
Set DBdb1 = CurrentDb
attachment = Me![Texrt8] ' This is the full path name of the attachment
End If
Set rst = DBdb1.OpenRecordset(mev3, dbOpenDynaset)
With rst
.MoveFirst
Do While Not rst.EOF
If rst!EMAIL <> &quot;&quot; Then

strEmail = !

Surname = ![FIRST_NAME] & &quot; &quot; & ![LAST_NAME] & &quot;,&quot;

Set objRem = objOutlook.CreateItem(olMailItem)
objRem.To = strEmail
objRem.Subject = Me![Text1] ' Subject line from driving form
objRem.Body = &quot;Dear &quot; & Surname & Chr(13) & Chr(13) & Me![Text3]
If attachment <> &quot;nil&quot; Then
objRem.Attachments.Add attachment
End If
objRem.Send
'on fail to send displays email to allow hand edit
If Err.Number <> 0 Then
objRem.display
Err.Clear
End If

End If

.MoveNext
Loop

End With

 

[annmariebette]

Dear Irish1957, Thank you for submitting your code for my review. I read through the code trying to figure it out. Does it assume the record set is the table (or query) behind the form? Do I need to change the references in brackets to my textbox references? How does it pick up the report I need to send as an attachment?
You can e-mail me at work at:
bettea@newegypt.us

Appreciate your help,
Ann Marie