Solved

Send Email (Outlook 2010) from Access Table

Posted on 2014-04-03
12
699 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
  • 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
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!

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Export Outlook 'ghost contacts' 11 38
Hide shared folder for some users 2 31
OUTLOOK 2016 receive time interval 8 54
run FileCopy code from a string 6 21
In this step by step procedure, you will come to know the details of creating an Outlook meeting in 2007, 2010, 2013 & 2016.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 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