ACCESS FEED: Re: [MS_AccessPros] RE: How to send emails through outlook in access 2003

Kamis, 06 Maret 2014

Re: [MS_AccessPros] RE: How to send emails through outlook in access 2003

Khalid-

If you are not sending attachments, you need to change Bill's function. The first line needs to look like:

Public Function Outlook_SendEmail(ByVal strTo As String, _
ByVal strSubject As String, _
ByVal strMsg As String) As Boolean

And then delete this code:

  With outItem.Attachments
        For lngAttachment = LBound(AttachmentList) To UBound(AttachmentList)
            .Add AttachmentList(lngAttachment)
        Next lngAttachment
    End With

To send your email to one list, first you need to call the MakeRecptString function:

Dim strRecipients As String

' Build the recipient list

strRecipients = MakeRecptString("<name of email field>", "<name of your table>")

That puts all the email address found in your table in one string to pass to Outlook. By the way, your form does not need to be bound to the table to do this. (Does not need to be in the Record Source.)

Then to send your email, do this:

Call Outlook_SendMail(strRecipients, "subject of your email", "Message….")

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Mar 6, 2014, at 8:06 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

Hi All,

I have waited for a week. But yet not get any further assistance to my reply............................

Khalid

On Wednesday, February 26, 2014 2:58 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

Bill,
I have renamed the Module "basSendEmail" which contains Function Outlook_SendEmail

Made a form "Send Email to Members"
Its Record Source is table "Members", I did not put any control on this form, except one Command button Name "CmdSendEmail"

On this form's code i copied your Function MakeRecptString (Am i right here OR something else is to be done?)

Now on Click event of ""CmdSendEmail"". What i have to write? this i am not getting.

Khalid

On Wednesday, February 26, 2014 1:19 AM, "wrmosca@comcast.net" <wrmosca@comcast.net> wrote:

Khalid
You should never name a module the same as any procedure (Sub, Function). It must have a unique name. I preface my modules with "bas" as that is an old standard back in the days of VB6.

And, yes, putting it in a separate standard module is what you should do. I gave you an example of how to call it.
-Bill

---In MS_Access_Professionals@yahoogroups.com, <khalidtanweerburrah@yahoo.com> wrote:

Hi Bill,
You wrote: The function Oulook_SendEmail should be copied into a standard module. It can be called from a button's click event on your form.

Should it be copied on database window's "Module" or on the form you mentioned to Create a new form.
My knowledge is limited to call a function. What i have done before in my other database is as follows, seeing that you will guess how much i know about it:

Option Compare Database
Option Explicit
Function RateWithNonBranded()
Amount = Rate * WeightOfCarton
End Function
Function RateWithBranded()
Me.Rate = Rate + 0.5
Amount = Rate * WeightOfCarton
End Function
---------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
'I have excluded other lines in this event, only copying related to function
' If Rate is empty
If IsNull(Me.Rate) Then
' Copy Rate from cmbSetRate
Me.Rate = cmbRate
End If
'-------------------
' If is empty BrandName
If IsNull(BrandName) Then
RateWithNonBranded
End If
'-------------------
If Not IsNull(BrandName) Then
RateWithBranded
End If
'-------------------
End Sub

I am storing email addresses of Members in table "Members" in a text field "MemberEmail"

Upto now i have copied your Function Outlook_SendEmail in a new module and named it "Outlook_SendEmail"

Could you please take me a step ahead.

Regards,
Khalid

On Monday, February 24, 2014 8:45 PM, "wrmosca@comcast.net" <wrmosca@comcast.net> wrote:

Khalid
Yes you will have to create a form just like you have to do with any task for the user. The article gives you most of the instructions, but as I said, I spotted a few syntax errors in it. My procedure does not have any errors. I've used it successfully for years. The function Oulook_SendEmail should be copied into a standard module. It can be called from a button's click event on your form. If you are going to use VBA code you really need to learn the basics. Do you know how to call a function from a click event? It is very difficult for us to do all of this from the ground up as we don't have your database.

Where are you storing the email addresses of the members?

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com

---In MS_Access_Professionals@yahoogroups.com, <khalidtanweerburrah@yahoo.com> wrote:

Hi Bill & John,

I am still waiting for any response.

Khalid

On Thursday, February 20, 2014 3:50 PM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

Bill,
Thank you for reply.
At present i have no idea from where i will send mails to Members. Do i have to make a separate Form for that purpose? and are there some extra settings to generate emails. Suppose the code you gave, where should it go. And the code on your site www.thatlldoit.com is also to be used somewhere.

I may have situations that i may be sending mails to a group of "MemberEC" this field is set to "Yes/No".

Sometimes to all members and in other situation selecting one "MembershipTypeID" or selective "MembershipTypeID" (every Member has a "MembershipTypeID"), "Membership" field describes the name of "MembershipTypeID" like:
MembershipTypeID Membership
LIF Life
COR Corporate, and so on...
Also each member has its "MemberID"

Could you please guide and help from where i should start for it and what i have to do?

Thanks in advance,
Khalid

On Wednesday, February 19, 2014 9:37 PM, "wrmosca@comcast.net" <wrmosca@comcast.net> wrote:

Khalid
That article should get you started, but you will have to create a function to make a list of your members. I see some syntax errors in that code. You might want to use the one I wrote. You can copy it from my site, http://www.thatlldoit.com/Pages/codesamples.aspx

I assume you are storing the member email addresses in your Members table. Here is a function that will create the list.

Function MakeRecptString(strField As String, strTable As String, _
Optional varCriteria, Optional varOutFile)
'Purpose : Concantenate list of recipients in a table.
'DateTime : 12/19/2000 08:24
'Author : Bill Mosca
'Return : String for To list.
'Optional : If varOutFile
Dim strRecpt As String
Dim strSQL As String
Dim intFileNum As Integer
Dim strRename As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

strSQL = "Select [" & strField & "] From [" & strTable & "] "

If Not IsMissing(varCriteria) Then
strSQL = strSQL & "WHERE " & varCriteria
End If

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If rs.EOF Or rs.BOF Then
MsgBox "Email List table not found. Process failed"
Exit Function
End If
With rs
Do While Not .EOF
strRecpt = strRecpt & .Fields(0) & ";"
.MoveNext
Loop
End With

If Not IsMissing(varOutFile) Then
'Create text file for strRecpt.
If Dir(varOutFile) <> "" Then
strRename = InputBox(varOutFile & " already exists. " _
& "Enter a new name for existing file to save it or " _
& "leave box blank to overwrite it.", _
"Rename File?", varOutFile)
If strRename <> "" Then
Name varOutFile As strRename
Else: Kill varOutFile
End If
End If


intFileNum = FreeFile
Open varOutFile For Append As intFileNum
Print #intFileNum, strRecpt
Close intFileNum
End If

Set rs = Nothing
Set db = Nothing

MakeRecptString = strRecpt

End Function

To use it in your routine to send the email with the members list change this line:
outItem.To = strTo

To this:
Dim strList As String
strList = MakeRecptString("NameOfEmailField", "NameOfMemebersTable",)
outItem.To = strList

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com

---In MS_Access_Professionals@yahoogroups.com, <khalidtanweerburrah@yahoo.com> wrote:

Hi all,
There has a situation arose in my new database, where the requirement is to send emails through Outlook to a group of "Members" and sometimes to individual "Member". OR sometimes a new event is taking place and we want to inform our "Members".

How do i set this?

I have read the following article
http://msdn.microsoft.com/en-us/library/office/aa159619(v=office.11).aspx

Should i follow this article or somebody could help and guide through the process of setting up all the way.

Thanks in advance
Khalid