Re: [MS_AccessPros] Sending email from access

 

Hi Crystal,

I have a Continuous form "Notification3" which displays brief information of members whom birthday is on the current date. Having Record Source:

SELECT Members.MembershipType, Members.MembershipTitle, Members.MemberID, Members.SurName, Members.GivenName, Members.GenderStatus, Members.DesignationSOC, Members.Dob, Members.PlaceOfBirth, tblContactNumbers.TelephoneResidence, tblContactNumbers.MobileNo1, tblElectronicContacts.MemberEmail

FROM (Members INNER JOIN tblContactNumbers ON Members.MemberID = tblContactNumbers.MemberID) INNER JOIN tblElectronicContacts ON Members.MemberID = tblElectronicContacts.MemberID

WHERE (((Month([Dob]))=Month(Date())) AND ((Day([Dob]))=Day(Date())));

Could this be used or modified for email addresses ?

I have taken some time on:

http://www.accessmvp.com/strive4peace/VBA.htm

and seen the video for Tab Crystal's Analyzer

 

 

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

hi Khalid,

you're welcome

>> there should be some

code which picks email addresses automatically and send this mail automatically. <<

yes there could  --  first is to step through it manually. Start with what you know how to do.  Use a set of test data going to email addresses it is ok to experiment with. Make a query to gather the information and construct what each email needs such as as  MailTo, Subject, Body (message) and the path\file that needs to be attached.

... one step at a time -- and speaking of time, you have not had sufficient time to read and absorb what is in the references I gave you ...

Please start a new question once you have done this.

thanks

respectfully,
crystal
 
~ have an awesome day ~

On 6/13/17 12:01 PM, khalidtanweerburrah@... [MS_Access_Professionals] wrote:

Hi Crystal,

Thanks for putting me my first step on the road to accomplish this task with your help & guidance.

The code is as follows now, on Ln 23 i made the change to test the code as you told to click anywhere on the code and press F5.

   sPathFileAttachment = "C:\Users\tanwe\Desktop\Today for sending email.txt" 'or whatever this is

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Option Compare Database

Option Explicit

Sub EmailOutlook()

'strive4peace

   'use early binding to develop

'   Dim appOut As Outlook.Application _

      , oMsg As Outlook.MailItem

   'use late binding to deploy

   Dim appOut As Object _

      , oMsg As Object

   Dim sPathFileAttachment As String _

      , sSubject As String _

      , sBody As String _

      , sMailTo As String

   

   'this information could be passed as parameters

   sMailTo = "someone@..."

   sSubject = "My subject line"

   sBody = "My message"

   sPathFileAttachment = "C:\Users\tanwe\Desktop\Today for sending email.txt" 'or whatever this is

   'sPathFileAttachment = "c:\path\file.ext" 'or whatever this is

   

   Set appOut = CreateObject("Outlook.Application")

   Set oMsg = appOut.CreateItem(0) '0=olMailItem

   With oMsg

      '.Importance = olImportanceHigh

      .To = sMailTo

      '.CC = "cc email address"

      '.BCC = "cc email address"

      .Subject = sSubject

      .Body = sBody

      If sPathFileAttachment <> "" Then

         .Attachments.Add sPathFileAttachment

      End If

      

      ' If you want to edit before sending

      .Display

      'otherwise, to just send without looking...

      '.Send

   End With

   

Proc_Exit:

   On Error Resume Next

   'release object variables

   Set appOut = Nothing

   Set oMsg = Nothing

   Exit Sub

   

Proc_Err:

   MsgBox Err.Description, , _

        "ERROR " & Err.Number _

        & "   EmailOutlook"

   Resume Proc_Exit

   Resume

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I did the Debug-->Compile, there was no error.

The code ran successfully and opened outlook, i sent a test mail to my own @hotmail address and received it there.

Now what to do further ?

My main thinking was that there should be some code which picks email addresses automatically and send this mail automatically. The scenario up to now looks to me that i will have to check myself which members birthday is occurring on the current date and put their email addresses manually. Another question just now stroked in my mind now is that if some members birthday on our weekend/holiday how will we handle this situation ?

Anyways i'll move ahead with your suggestions and guidance.

Thanks again,

Regards,

Khalid

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

hi Khalid,

you're welcome

>> The question is at which place i put this code ? In

modules or somewhere else. <<

make a new module. Make a blank line below the compiler directives at the top and paste the code. Then debug, compile, and save. Name the module --> mod_EmailOutlook

substitute your data then debug, compile, and save again. Then run the code.

Once you test it stand-alone, then the code can be modified to take parameters and be called from other places. Visit the links I gave you and read the books. That will be a great help to your understanding.

*** How to Create a Standard (General) Module ***

Press Alt-F11 to go to the VBE (Visual Basic Editor)

From the menu in a the Microsoft Visual Basic window:
Insert --> Module

Paste code after compiler directive(s) -- anything starting with "Option" at top

once the code is in the module sheet, from the menu, do -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen -- in this case, nothing happening is good <g>

if you do have errors, Fix any errors on the highlighted lines.
Add needed references, remove unnecessary references, resolve missing references
(from the menu: Tools, References...)

keep compiling until nothing happens (this is good!) -- then Save

Click the diskette icon on the toolbar to save the module. Make sure to give the module a good name when you save it.  You can have several procedures (Subs and Functions) in a module, which gives you a way to categorize them ... ie: basic procedures that would be useful in any database; procedures that are specific to a particular database; procedures for converting data; etc.

IMPORTANT: do NOT name the module the same as any procedure. Give it a unique name like "mod_whatever" or "bas_whatever"

~~~~~ also be sure, before compiling, to use Option Explicit at the very TOP of each module so variables that are not declared or are misspelled will be picked up by the compiler as well as many other errors than can be fixed before running.

Option Explicit ' require variable declaration

~
To run the code:
Since this is a sub with no parameters, you can click anywhere in it and press F5 to run.
OR
Choose Run, Run Sub/UserForm from the menu

~~~
It is not hard to learn the basics of VBA ... it makes sense. The 3 chapters (all I wrote) posted here will not take you long to read. Chapter 1 ... maybe 20 minutes. Chapters 2 and 3 you can read for logic but not memorize -- they are there for reference and foundation.

Learn VBA
http://www.AccessMVP.com/strive4peace/VBA.htm

this should give you the knowledge you need to understand basic code.

~~~

you should also read the free Access Basics book. Bill has it on his website:

http://www.thatlldoit.com

respectfully,
crystal
 
~ have an awesome day ~

On 6/13/17 9:37 AM, khalidtanweerburrah@... [MS_Access_Professionals] wrote:

Hi Crystal,

Thanks for your swift reply.

I have a question which might be silly for you, but as i'm novice. I always admit i am on the first step of programming. The question is at which place i put this code ? In modules or somewhere else.

Please don't mind it if i ask such questions further also.

Regards,

Khalid

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

hi Khalid,

start with this:
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub EmailOutlook()
'strive4peace
   'use early binding to develop
'   Dim appOut As Outlook.Application _
      , oMsg As Outlook.MailItem

   'use late binding to deploy
   Dim appOut As Object _
      , oMsg As Object

   Dim sPathFileAttachment As String _
      , sSubject As String _
      , sBody As String _
      , sMailTo As String
  
   'this information could be passed as parameters
   sMailTo = "someone@..."
   sSubject = "My subject line"
   sBody = "My message"
   sPathFileAttachment = "c:\path\file.ext" 'or whatever this is
  
   Set appOut = CreateObject("Outlook.Application")
   Set oMsg = appOut.CreateItem(0) '0=olMailItem

   With oMsg
      '.Importance = olImportanceHigh
      .To = sMailTo
      '.CC = "cc email address"
      '.BCC = "cc email address"
      .Subject = sSubject
      .Body = sBody
      If sPathFileAttachment <> "" Then
         .Attachments.Add sPathFileAttachment
      End If
     
      ' If you want to edit before sending
      .Display
      'otherwise, to just send without looking...
      '.Send
   End With
  
Proc_Exit:
   On Error Resume Next
   'release object variables
   Set appOut = Nothing
   Set oMsg = Nothing
   Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   EmailOutlook"

   Resume Proc_Exit
   Resume
End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

substitute in a test set of values and see if you can generate the email from Access.  You must be using Outlook on your machine, not in the cloud.

Then you can merge in the things you need from Bill's code. Start simple then build bigger :)

respectfully,
crystal
 
~ have an awesome day ~

On 6/13/17 8:58 AM, khalidtanweerburrah@... [MS_Access_Professionals] wrote:

Hi Crystal,

Now my outlook is working.

So please let's start the task. Definitely you will take me further step by step ahead.

Thank you in advance for your future help and assistance.

The code which i mentioned before are all in message history, please do check them if any modification or amendment is required please mention it.

regards,

Khalid

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

Hi Crystal,

Thanks for replying. I got it that specific mail application such as Outlook is required to send attachments.

I'm trying to configure my Outlook account, its not configuring yet. As soon it is working and OK. I'll return and request you to guide me further.

Thanks again for replying.

Best regards,

Khalid

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

Hi Khalid,

>>"with an attachment <<

unless you automate with a specific mail application such as Outlook, I do not believe you will be able to attach anything. As far as I know, it is not possible to attach files using Application.FollowHyperlink  "mailto:  ....

respectfully,
crystal
 
~ have an awesome day ~

On 6/11/17 9:57 AM, khalidtanweerburrah@... [MS_Access_Professionals] wrote:

Hi Crystal,

First of all sorry for replying much late, as i was engaged on some other issues.

Now the current situation is that my user do not have Microsoft Out Look Express, so he will use some other email probably gmail.

The first task which i need to accomplish is that when a Member/Members birthday occurs on the current date an email be sent to him/them automatically (with an attachment of Happy birthday wish from Society, some image selected and edited to our requirements).

The necessary fields from table "Members" are as follows:

MembershipType-->Text-->3

MemberID-->Number-->Long Integer (PK)

SurName-->Text-->30

GivenName-->Text-->30

GenderStatus-->Text-->15

Dob-->Date/Time, Format-->Short Date, Input Mask-->00/00/0000;0;_ (Date of Birth)

tblElectronicContacts

MemberEmail-->Text-->50

I have already have a Continuous Form "Notification3", which opens on clicking the Label "LabelNotification3" with the Caption "Member's Birthdays Today".

Form "Notification3" Record Source is query "qryHappyBirthday" having Sql:

SELECT Members.MembershipType, Members.MembershipTitle, Members.MemberID, Members.SurName, Members.GivenName, Members.GenderStatus, Members.DesignationSOC, Members.Dob, Members.PlaceOfBirth, tblContactNumbers.TelephoneResidence, tblContactNumbers.MobileNo1, tblElectronicContacts.MemberEmail

FROM (Members INNER JOIN tblContactNumbers ON Members.MemberID = tblContactNumbers.MemberID) INNER JOIN tblElectronicContacts ON Members.MemberID = tblElectronicContacts.MemberID

WHERE (((Month([Dob]))=Month(Date())) AND ((Day([Dob]))=Day(Date())));

I have just put this information may be you ask me about it.

Crystal, please assume that i am a novice so you will have to guide me step by step.

Required your help.

Regards,

Khalid

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

with Application.FollowHyperlink and mailto:, you can use the default email package installed but you won't have as much control over the message. For instance:

   On Error Resume Next
   Application.FollowHyperlink _
      "mailto: TheEmailAddress@...?subject=My Subject " _
      & "&body=whatever you want to say"

better than using spaces would be to use %20 ... for instance:

?subject=My%20Subject

There are other parameters such as cc and bcc that can be specified. The first parameter has "?" before it and the rest then have "&" before them. Line breaks are represented with: %0d%0a

respectfully,
crystal

http://www.MsAccessGurus.com
connect to me, let's build it together
 
~ have an awesome day ~

On 5/14/2017 7:26 AM, John Viescas JohnV@... [MS_Access_Professionals] wrote:

Khalid-

You have to have Microsoft Outlook installed on the machine where the app is running for that code to work.  If you use something else for email, you'll need custom code.

__._,_.___

---

Posted by: khalidtanweerburrah@yahoo.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (13)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___