Modules & VBA :: Concatenating Emails Into A String Via SQL

Sep 3, 2013

There are so many threads on here for emailing a PDF version of a report. I've put together a nearly complete bit of VBA that will do what I want - except for one thing.

Basically I have a personnel table with an email field. I Also have a field that details if a person is available or not ("Home" means they are available)

I need to create a string that is comprised of all the email addresses of the personnel who are "Home". In another thread I read that this can be done using a SQL statement.

So this is the VBA that I have so far - I commented the SQL for now because I don't quite know how to use it here.

Also, I have created the string, called "emailsList"

Code:
Private Sub Email_Button_Click()
'SELECT Personnel_Table.Email FROM Personnel_Table WHERE (((Personnel_Table.Status)="Home"));
Dim emailsList As String
DoCmd.SendObject acReport, "AWACT_Report", acFormatPDF, emailsList, , , "Training Update", "Attached is the newest Training Report.", True
End Sub

Do I need to do a loop that concatenates?

View Replies


ADVERTISEMENT

Modules & VBA :: Concatenating Data From Multiple Checkboxes

Apr 15, 2015

I have 8 checkboxes. Each checkbox has several e-mail addresses as string. Therefore, each checkbox has a string variable declared. I was wondering what should I do when selecting multiple check boxes. This is my code:

Code:
If Me!chkAGDLLA = True Then
Forms!email.lstName = Null
Forms!email.chkComercial = False
Forms!email.chkOperacional = False
strAGDLLA = "email1, email2, email3, email4"
strMail = strAGDLLA & ", " & strISA & ", " & strMAYA & ", " & strSANGER & "," & strSANSE & ", " & strSede & ", " & strGC & ", " & strCSR
MsgBox strMail
End If

Problem is that if I only select chkAGDLLA, then strMail will be "email1, email2, email3, email4, , , , , , ,"

I do not want all those commas, but how to make this work. I was thinking maybe a SELECT CASE so that strMail will accumulate data based on what's checked, but then there will be a problem of there being no comma between cases.

View 6 Replies View Related

Modules & VBA :: Separate Numbers And String From Alpha-numeric String

Jun 7, 2013

MS-Access VBA code to separate numbers and string from an alphanumeric string.

Example:

Source: 598790abcdef2T
Output Required: 598790

Source: 5789065432abcdefghijklT
Output Required: 5789065432

View 13 Replies View Related

Modules & VBA :: Run Time Error 287 When Sending Emails

Dec 20, 2013

I have the following code in a module that sends out various email updates to me when I'm off site. On my PC it works perfectly whether I have Outlook open or not. I've installed the database on several other machines, but I'm getting a Run Rime Error 287 on the .Send line if the user has Outlook closed. Any setting somewhere in Outlook or Access that needs changing? Or is there something that can be changed in the code to ensure the email sends whether Outlook is open or not?

Code:
Public Sub SendEmailNotification(strSubject as String, strMessage as String, strAddress as String)
Dim olEmailApp As Object
Dim olMsg As Object
Dim strEmailBody As String
Set olEmailApp = CreateObject("Outlook.Application")
Set olMsg = olEmailApp.CreateItem(0)

[Code] ....

View 1 Replies View Related

Modules & VBA :: Sending Multiple Emails Using Outlook

May 7, 2015

Using Access 2010; I have code that generates multiple emails to send using outlook. It works fine except that all emails go to my draft folder and I have to go the outlook and send them individually. This is fine when I have under 10 emails; but sometime it can get up to 50 or even 100 emails. What I have to do to send them from my draft all at once. The emails go to draft to inspect for errors.

View 1 Replies View Related

Modules & VBA :: Choosing Specific Font For Emails

Jan 30, 2015

I would like to have Access launch an Outlook email with a specific font (Arial). How could that be accomplished?

Here's my code:

Private Sub cmdEmailAO_Click()
On Error Resume Next
Dim OApp As Object, OMail As Object, signature As String, Email As String, FirstName As String
Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)

[Code] ....

View 9 Replies View Related

Modules & VBA :: Number Of Unread Outlook Emails

Aug 4, 2014

I need to write a vba code that tells me number of unread and incoming emails in an inbox in outlook. I managed to link to inbox in outlook, but all I need now is to write the code to give me:

a) number of unread emails
b) fill some of unread emails details (like name, from) in a listbo

View 1 Replies View Related

Modules & VBA :: Send Emails To Defined Array Of Recipients Using CDO

Sep 18, 2013

I am trying to send emails using CDO to an array of recipients loaded from a query. I have made this work using Lotus Notes so i know the first part of my code is correct, and the current code I have works when I input an actual email address. But when I try to add the Array name into the 'To' field I get a data mismatch error.

View 3 Replies View Related

Modules & VBA :: Retrieve Count Of Emails In Outlook Subfolders

Jun 28, 2014

I need to retrieve the count of emails in a shared exchange mailbox in Outlook 2010. I found the following software that retrieves the count I need, but only in default folders or ones that are 1 layer deep. The folder I need is two layers deep Mailbox -> Inbox -> Completed. Is there anyway to drill down just one more level?

Const olFolderInbox = 6
Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objInbox = objNamespace.GetDefaultFolder(olFolderInbox)

[code]....

View 6 Replies View Related

Modules & VBA :: Loop Through Query Results To Output Emails

Jan 29, 2015

I have a query that randomly selects records from my table:

SELECT TOP 6 *
FROM SM_Import
ORDER BY rnd(INT(NOW*id)-NOW*id);

I would like to loop through those results, and create emails

I can get one email formed (displayed) with the code below but don't know how to get both working together to display the (6) emails:

Private Sub Command3_Click()
Dim ol As Object
Dim CaSubject As String

[Code].....

View 4 Replies View Related

Modules & VBA :: Update Subject Of Emails In Outlook That Have Already Been Received

Jul 22, 2014

I'm trying to automate some processes. My company uses Outlook 2010 and every day we need to go into our "Sent" folder and rename the Subject line of all of our sent emails. We change the subject to a standardized format depending on the content of the email and who the email was sent to. These are then used as part of an equation to tally our Transactions Per Hour. (TPH)

I've already designed a small Access 2010 utility to track the TPH, but I would like to add the functionality to parse some data from the subject lines of my "Sent" folder, and automatically make changes to save some time.

Here is my thought process:

- Click "Process Sent Emails" button from TPH utility.
- Access runs through each email in the "Sent" folder (or perhaps only selected emails) and changes the Subject line per simple Select Case criteria (I can figure that part out).

View 2 Replies View Related

Modules & VBA :: Create Emails And Manage Replies In Access 2010

Jan 25, 2015

I found the below link that looks great and works.

[URL] ....

Te only thing is that I don't have much control over it and would like to build something similar as we also need to work with access 2003 (still )Any way to find how the code works (if there is some of it hidden in access)?

View 6 Replies View Related

Modules & VBA :: Create A Recordset For Multiple Emails And Send One Email

Mar 17, 2014

I have some code that takes from a query, an email address and sends out an email.Due the security warning that happens for each individual email, i want to create ONE email for all email address in the query and populate them to the BCC section of the sendobject.Here is the code that I currently use

Code:
Private Sub cmdSendEmail_Click()
Dim MyDB As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String

[code]...

I want to populate the BCC field with multiple emails addresses seperated by ":" and send just one email.

View 4 Replies View Related

Modules & VBA :: Selecting Recipients From A Table For Sending Emails? Access 2007

Jul 19, 2013

In an Access 2007 module, is there a way of sending an email to a list of people stored as a list in a table in the database, rather than having to put all the names into the function?

I have a function I'm using to generate and send out an email to certain people, but the list is constantly growing so I'm looking for a better way to manage it! The main issue is having to kick users out of the database every time I need to update the recipients list... because it's stored in the code.

If I create a simple table containing all the names, how could I then ask it to use that instead?

This is the function I'm using currently - found on here and adapted to my own purposes

Syntax to use for the function: SendNotesMail "recipients", "Body Message", "Additional Text", "Subject"

Code:
Public Function SendNotesMail(strSendTo As String, strBody As String, strExtraText As String, strSubject As String)
'This public sub will send a mail and attachment if neccessary to the recipient including the body text and additional comments from the Active record
DoCmd.OutputTo acOutputReport, "REP09emailnotification", acFormatRTF, "x: endersgroup tendering databaseTenderUpdate.rtf", False
Dim Subject As String
Dim Attachment As String

[Code] ....

So I guess my question is: how do I get my recipients from a table into the strsendto?

View 5 Replies View Related

Modules & VBA :: Requirement To Break Bulk Emails Into Smaller Groups For Transmission

Mar 10, 2014

I have put together a VBA sub to run Outlook.Application to send a report to selected email addresses. A Table's records contain an email addresses as well as category for grouping purposes.

I use a "Create Table Query" to extract the selected email addresses from the table into a temporary table called "ETransferAddress" then "Set rst = CurrentDb.OpenRecordset("ETransferAddress")" then concatenate the email addresses separated with commas.

This works fine for small groups, but the service provider blocks them if the number of email addresses go into the hundreds.

What I need is to break the email addresses into sub groups and then create a series of emails all with the same Report, Subject and Content.

One way might be to create a series of loops to create a number of smaller tables but to string that lot together to achieve it is beyond me at present.

Here is my code to date"

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim strPath As String
Dim strFilter As String
Dim strFile As String
Dim rst As DAO.Recordset
'Create a table with selected addresses

[Code] .....

View 2 Replies View Related

Modules & VBA :: Sending Emails To Multiple People Based On Same Query And Report

Jul 15, 2013

At present I have a tblData, a tblPeople, a long union query and a report that displays its results. On a form I select a person from tblPeople (another field on that table contains their email address), run the query and report in that person's respect and email the results to them.

What I want to do is add a button which runs some VBA code which cycles through tblPeople, runs the query for each person, works out if the query has returned any records from tblData for that person, and if so - sends them the report.

I am using Access 2010

View 4 Replies View Related

Modules & VBA :: Auto Create Emails In Outlook Based On Data In Access?

Apr 20, 2015

I have a table called Contacts in Access:

ID Email
001 email1
002 email2
003 email3
004 email4
008 email5
010 email6

And I have a table called Data in Access:

ID Date Person Title Yes/No Action
001 20/4 Mr A Consultant
001 20/4 Mr B Supervisor
001 20/4 Mr C Consultant
004 20/4 Mr D Consultant
010 20/4 Mr E Consultant
010 20/4 Mr F Supervisor

What I'm looking for is coding that would start with the first ID number in CONTACTS and see if there is the corresponding ID in DATA. If there is, then I want it to create an Outlook email and have:

1.To field populated with the email address in the CONTACTS table

2.From and CC fields to be my email address

3.The subject line needs to read [ID] "Request" and then today's date backward (e.g. 20150420).

4.The body I need to put some generic wording and then a table with the records from the DATA table (first 5 fields plus the headings).

5. The format of the table etc isn't important as long as it is a table format

If it's created the email I want it to go back to the DATA table and put "Email Created" in the Action column for those IDs.

I don't want the email to automatically send; I only want it to create a draft (as this data needs to be manually confirmed before sending).

If for example it gets to checking in DATA table for ID 002 (and as seen there's none there), I want it to skip creating the email and move on to the next ID.

In the end I would expect to see 3 draft emails open in Outlook ready to be sent, looking something like the below (just 001 below)?

From... my email
To... Email1
CC... my email
Subject: 001 Request 20150420

action the below:

ID Date Person Title Yes/No
001 20/4 Mr A Consultant
001 20/4 Mr B Supervisor
001 20/4 Mr C Consultant

And the DATA table look like this (if only 001 email was created):

ID Date Person Title Yes/No Action
001 20/4 Mr A Consultant Email created
001 20/4 Mr B Supervisor Email created
001 20/4 Mr C Consultant Email created
004 20/4 Mr D Consultant
010 20/4 Mr E Consultant
010 20/4 Mr F Supervisor

I will need to add more data to the CONTACTS table over time, also the DATA table will be different each day so I need the coding to accomodate for both of these possibilities.

View 9 Replies View Related

Modules & VBA :: Using Mid In A String

May 1, 2014

I'm trying to get a value from a spreadsheet to import into my MS Access database. Currently I am trimming the spaces/carriage returns from it but need to strip some more data from the value.

Here is my code.

trimmed_department = Trim(Replace(new_department, vbCrLf, ""))

Example value being "123 Point 5 Finance and Accounting"

I want to use Mid (I think?) to remove the "123 Point 5" (it is always the same with no exceptions) but don't know how to use it as I don't know how to use multiple parameters within a string.

View 8 Replies View Related

Modules & VBA :: How To Add A String To A Date

Feb 27, 2014

I want to add a string as year to a date.

Somehow it doesn't work out. It should extract all records with Valid_from = 01.01.2013 and valid_to = 31.12.2013. The Year assignment works.

Code:
Public Sub BEN()
Dim strSQL As String
Dim t As Date, s As Date
DoCmd.SetWarnings False
Year = Right(pricedate, 4)
t = 1 / 1 / " & Year & "
s = 12 / 31 / " & Year & "
strSQL = "SELECT TRANSFER_PRICES.* INTO [TEMP] " & _
" FROM TRANSFER_PRICES " & _
" Where [Valid_from] = " & Format(t, "#mm/dd/yyyy#") & _
" AND [Valid_to] = " & Format(s, "#mm/dd/yyyy#")
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub

View 3 Replies View Related

Modules & VBA :: Add A String On Top Of The Calendar?

May 19, 2015

I have set calendar control 12.0 up and everything works how I want it to (click date and peoples names in a table to the left to show scheduled meetings on that day). What I want is to add a string on top of the calendar. For example, this monday I would like for it to say "Memorial Day" on the physical calendar itself.

View 3 Replies View Related

Modules & VBA :: Cut Off First 6 And Last 2 Characters Of String

Mar 24, 2014

I would like to cut off the first 6 and last 2 Charaters in an after update event but not sure how, I cannot use mid as the length of the string may change but never the first 6 or last 2, can some one show me how it's done ...

View 4 Replies View Related

Modules & VBA :: Splitting A String

Oct 5, 2013

I have made a system for managing service calls and fieldworks.Part of this is checking the boards we install/service are working correctly.When the engineer calls in, we check the board - enter serial number, via ODBC talks to main server DB and pulls back what is listed below, along with ID and date/time which is all displayed in a list box.

9853911264,W-AMR,3,2:320:0:52,MAIN STORE,3.57,0,18,001.004.041,0,0*75

This works fine. In the string above are certain bits of information that need to be checked to ensure they are accurate and the board has been programmed correctly. What I want to do it, is when the user single clicks on the list box, it parses out the various sections of the above string and enters them into some text fields on the form. With some code these can then be checked to see if they are correct or not and alert the user if they are or not.

The checking part I can do, the part I am struggling with is parsing out the correct parts from the string.The parts will be the same parts required each time, and the string parts wont vary, just not sure how to go about it.

View 13 Replies View Related

Concatenating In Reports

Oct 11, 2005

I have a firstname field, and a surname field.

On a report I have a textbox that is bound to =[firstname] & " " & [surname]

This gives me an error. I have even tried single quotes, but no luck.

Please advise

Thank you in advance

View 2 Replies View Related

Concatenating Problem

May 28, 2007

Hi All:

, I have two fields to concatenate.

SKU = ps1-
Supp_sku = 98756

I want to have it like ps1-98756.

I have used the following query to test the result -

select sku & “ “ & supp_sku as [my_sku] from inventory_master;

The result is
My_sku = 198756

I do not get ps1-98756.

Could you please help?


Thanks - Prabir

View 9 Replies View Related

Help With Concatenating Fields

Jun 11, 2006

G'day,

I want to produce some UPCa bar codes which require 11 digits of TEXT for the barcode function to work.

I want the bar code to be made up of several fields
Make 3 Chars (Text) (eg 021)
Model 3 Chars (Text) (eg 103)
Serial_Number 5 Characters (Numeric) (eg 00025)
(I need to keep the Serial_Number field numeric so that it increments in modulo 10 format (ie get Numeric only results))

So i need to produce a field that looks like this 02110300025 after the 3 fields are concatenated

However I am getting 02110325

I'm sure that the answer is simple but I seem to be having a mental block. Does anyone have any ideas? Your help will be very appreciated.

View 5 Replies View Related

Concatenating Instead Of Adding

Jan 9, 2005

I am trying to create a basic query that adds four numbers, but when I try '[Mark1]+[Mark2]+[Mark3]+[Mark4]' it concatenates it instead of adding.
e.g. I have '58+96+13+45' and I get '58961345' instead of 212.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved