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

 

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

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (7)

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___