Solved

Send Email (Outlook 2010) from Access Table

Posted on 2014-04-03
12
702 Views
Last Modified: 2014-04-03
I need to send emails as a group from a Access table with attached Excel spreadsheet on a command button.
0
Comment
Question by:shieldsco
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39975143
try this codes


Dim objOutlook As Object, objEmailMessage As Object
Dim sSubj As String, sBody As String, sTo As String, strCC As String
Dim xlPath as string
xlPath = "c:\folderName\myexcel.xls"


Set objOutlook = CreateObject("Outlook.Application")
Set objEmailMessage = objOutlook.CreateItem(0)

With objEmailMessage
         .To = sMail
         If strCC & "" <> "" Then
                    .CC = strCC
         End If
         .Subject = sSubj
      .Attachments.Add xlPath

      .display
      '      .send
End With


to get all the recipient emails, open the the table as recordset

dim rs as dao.recordset
set rs=currentdb.openrecordset("select email from tableName")

do until rs.eof
     sMail=sMail & ";" & rs("email")
    rs.movenext
loop

sMail=mid(sMail,2)
0
 

Author Comment

by:shieldsco
ID: 39975707
It opens Outlook and attaches file however the code does no populate the email address in Outlook
0
 

Author Comment

by:shieldsco
ID: 39975951
Email address is not populated in Outlook
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:shieldsco
ID: 39975965
Does the email field have to text? Currently I have it as a hyperlink
0
 

Author Comment

by:shieldsco
ID: 39975967
Below is the code

Private Sub Email_Click()


Dim objOutlook As Object, objEmailMessage As Object
Dim sSubj As String, sBody As String, sTo As String, strCC As String
Dim xlPath As String
xlPath = "\\cdc\project\OD_FMO_DEBT_MGT\PA Invoice Split Database\Email\Invoice_Email.xlsm"





Set objOutlook = CreateObject("Outlook.Application")
Set objEmailMessage = objOutlook.CreateItem(0)

With objEmailMessage
         .To = sMail
         If strCC & "" <> "" Then
                    .CC = strCC
         End If
         .Subject = sSubj
      .Attachments.Add xlPath

      .display
      '      .send
End With



Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("tblStaff")

Do Until rs.EOF
     sMail = sMail & ";" & rs("email")
    rs.MoveNext
Loop

sMail = Mid(sMail, 2)




End Sub
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39976111
use this codes


Private Sub Email_Click()
Dim objOutlook As Object, objEmailMessage As Object
Dim sSubj As String, sBody As String, sTo As String, strCC As String
Dim xlPath As String
xlPath = "\\cdc\project\OD_FMO_DEBT_MGT\PA Invoice Split Database\Email\Invoice_Email.xlsm"

Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("tblStaff")

Do Until rs.EOF
     sMail = sMail & ";" & rs("email")
    rs.MoveNext
Loop

sMail = Mid(sMail, 2)




Set objOutlook = CreateObject("Outlook.Application")
Set objEmailMessage = objOutlook.CreateItem(0)

With objEmailMessage
         .To = sMail
         If strCC & "" <> "" Then
                    .CC = strCC
         End If
         .Subject = sSubj
      .Attachments.Add xlPath

      .display
      '      .send
End With

End Sub


.
0
 

Author Comment

by:shieldsco
ID: 39976146
The code works fine however the TO : line in the outlook message is this format Adrianne.Thomas@va.gov#mailto: Adrianne.Thomas@va.gov# which outlook does not recognize. Any thoughts on fixing the problem
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39976180
dim varEmail as string

Do Until rs.EOF
varEmail=rs("email")
     sMail = sMail & ";" & Left(varEmail, Instr(varEmail,"#")-1)
    rs.MoveNext
Loop

sMail = Mid(sMail, 2)
0
 

Author Comment

by:shieldsco
ID: 39976215
I get run time error 94 invalid use of null on code:
varEmail = rs("email")
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39976253
dim varEmail as string

Do Until rs.EOF
  if rs("email") & ""<>"" then
       varEmail=rs("email")
           sMail = sMail & ";" & Left(varEmail, Instr(varEmail,"#")-1)
   end if
    rs.MoveNext
Loop

sMail = Mid(sMail, 2)
0
 

Author Closing Comment

by:shieldsco
ID: 39976270
Thanks for ALL YOUR HELP!!!!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question