VBA check for email subject line

Is there a way to get access to check to see if an email is present by looking at the date or subject line? If it is there I want to go ahead and kick off an autoexec macro. thanks


Post your answer or comment

comments powered by Disqus
Hi folks,

We use Notes as our intranet here, and I really wish we used Outlook. Why?

I need to be able to determine if an email has been received with a specific header. Via Access.

We have a db at one of our remote sites that the users there log daily delivery shortages onto. At the end of each day, this information is then emailed to us (via Notes), when the user clicks on an option on their menu.

What I would like to happen is for this data, once received in a mailbox with a specific subject line, to be automatically detected and the information contained therein imported into one of our other dbs. This would ideally be triggered automatically, however I am not averse to manually initiating the procedure via an option on the menu.

The ideal solution of course would be to use a SQL server or something to have a true multi-site DB, as I believe this would eliminate the need to send any data! Unfortunately the makeup of our network prevents from having this - the different sites on the WAN cannot see each other!

Does anyone have any suggestions? I can kind of visualise how I want to have this set up, but am clueless on how I would go about checking the contents of a mailbox.

Any thoughts would be appreciated,

Ad

I can't understand this. I have a form that takes name and address information. Then, after the person hits submit what should be simple vba code runs complaining if an email address is missing (if they are signed up for a email list by one of a few check boxes) or complains if parts of the address info is missing (if they signed up for paper mailings by check box).

I can't for the life of me see what the problem is below.


	Code:
	    ' check for email address before inserting record...
    If ( (ECNS_ThisWeek = 1) OR (ECNS_Insider = 1) OR (EP_ToTeach = 1) OR (EP_NewTitles = 1 Or EP_WhatsNew = 1) ) _
    And _
      (RTrim(LTrim(Me.Email)) = "" Or IsNull(Me.Email)) _
    Then
       MsgBox ("Please enter email address to receive email newsletters.")
    End If

    'check for complete mail address for paper newsletter
    If ( (NC_HelpingPeople = 1) OR (NC_Neighbors = 1) OR (NC_OneMission = 1) ) _
    And _
        (IsNull(Me.Fname) Or RTrim(LTrim(Me.Fname)) = "" Or _
        IsNull(Me.Lname) Or RTrim(LTrim(Me.Lname)) = "" Or _
        IsNull(Me.Addr1) Or RTrim(LTrim(Me.Addr1)) = "" Or _
        IsNull(Me.City) Or RTrim(LTrim(Me.City)) = "" Or _
        IsNull(Me.StateProvinceID) Or _
        IsNull(Me.Zip) Or RTrim(LTrim(Me.Zip)) = "") _
    Then
        MsgBox ("Please enter complete Mail address information to receive newsletters.")
    End If

Both portions are intended to work the same way... If any of the checkboxes are checked, and the elements/fields are null or blank then fire off the msg...

This only runs when the form is brand new and fresh. If I run this code on a brand new screen both msgs fire off... If I drop a cursor in on a field that is not even being checked it fails to run. I am totally blown away with frustration right now.

If someone could point me in the right direction/tell me what stupid thing I am doing wrong that would be great.

thanks,
-madEG

Greetings,

I have a form that has all of our file information. There are various parts to our files and each piece is ordered from a client by our analysts. I want to create an email button that automatically puts the Order Number, Client Name and Client Number in the subject line of the email. I'm 90% sure there is a way to do this, but I haven't found anything or been able to figure it out. I do have working email code, so I'm thinking it's just a matter of some magic words to make my code populate the subject line. Any help would be awesome. I am running MS Access 2003. Thanks!

I've been using the command of:

DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc][, subject][, messagetext][, editmessage][, templatefile]

to e-mail contents of a form. However, for the subject line, it's only allowing me to put 7 characters, otherwise, a run-time error occurs. Do you know why it could be doing this and how to fix it?

Thank you as always!

Hello - I have a form set up so when the sales person enters a date for a follow-up call it generates and e-mail reminder and sends it to the sales person on the day of the follow-up call. For the subject line I have outlook getting the infomation from the ClientStreet field. This works fine.

They would now like me to have both the address and the Project ID show up in the subject line but I have no idea how to add another field to the suject line. This is the code I currently have:

Private Sub FUDateContacted_AfterUpdate()
Dim appOutLook As Object
Dim MailOutLook As Object

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
.To = Me.EmailRecipient 'get the recipient from the form
.Subject = Me.ClientStreet 'get the street from the form
.body = "This is a reminder that you need to make a follow up call to the address in the Subject line today."
.DeferredDeliveryTime = Me.FUDateContacted 'get the date from the form

.Send
End With

Set appOutLook = Nothing
Set MailOutLook = Nothing
End Sub

Do I have to add another Subject line? or add code to the existing Subject line? I'm a little stuck on how to put in two fields so any help would be very greatly appreciated.

Laura

I've set up command button to email a snapshot result of a query. Is there a way to populate the subject line of the email relative to the query.

ex. I query for lname, fname, and cust# and other data for a report. The mail button throws a snapshot attachment into an email, I'd like the customer name and number to be in the subject line of the email.

thoughts?

thanks in advance
--ck

Hello everyone!

Here is the problem, I have an Access form and a textbox for an e-mail address. The email address is not required, I only want to check for a valid email address when a value or address has been entered into the e-mail filed. Example: If e-mail is not empty or if email field has a value, then check for the follow elements, matched the email address with the required format. If the e-mail field is empty, do not look for anything, move on.

Any ideas?

Avionicx

I have a form set up with multiple option buttons for the user to select a report to e-mail. The final option button is for the user to select "all" reports to e-mail. I am using Select Case and then on the "all reports" case, it has a DoCmd to open each report one at a time and send each via email as a snapshot. (I can't find a way to send multiple reports on one e-mail). The problem is, the user wants to attach a new subject line each week when the report is sent. I don't want it to stop on each report (the reports take a long time to run). I was thinking I could add a field to the form for the user to type in the new subject line for that week and have the code referenct that field, but I don't have a clue as to how to begin. Anyone with any ideas?? Thanks!!

I'm using MS Access 2010 and I have a bunch of Send Object emails that reference tables and queries and sometimes the attachments contain no data.

If "no data" record count=0, I would like to send the email attachment, but in the subject line indicate some additional text indicating "no data".

This way they know that the report was run, but I'm hoping they see "no data" within the subject line to know that they can delete the email.

Hi Forum, access 2000

I found an email database on the forum and got it to work for me but I have to close the database and reopen to send a 2nd email

Here is the full code which includes the original.
I have yet to edit out any surplus code.

Can anyone spot where I need to alter the code to allow more then one 'click" of the button.
Even if you select a new record, the button won't work.
Only works Once when Database is opened.


	Code:
	Private Sub CmdEmailPointsBalance_Click()
    Dim varTo As Variant                    'Email Address
    Dim stText As String                    'Email Text
    Dim stSubject As String                 'Email Subject Line
    Dim strSQL As String                    'SQL String
    Dim PointsAvailable As Integer          'Available Club Points
    Dim MembID As String                    'Member ID
    Dim errLoop As Error
    
    MembID = Me.MemberID                    'set forms current Member ID to be variable value
    PointsAvailable = Me.txtPointsBalance   'Set Forms Points Available to Use as variable value
    
     On Error GoTo Err_CmdEmailPointsBalance_Click

  '  Dim stWhere As String       '-- Criteria for DLookup
  '  Dim varTo As Variant        '-- Address for SendObject
  '  Dim stText As String        '-- E-mail text
  '  Dim RecDate As Variant      '-- Rec date for e-mail text
  '  Dim stSubject As String     '-- Subject line of e-mail
  '  Dim stTicketID As String    '-- The ticket ID from form
  '  Dim stWho As String         '-- Reference to tblUsers
  '  Dim stHelpDesk As String    '-- Person who assigned ticket
  '  Dim strSQL As String        '-- Create SQL update statement
  '  Dim errLoop As Error

    '-- Combo of names to assign ticket to
  '  stWho = Me.cboAssignee
  '  stWhere = "tblUsers.strUserID = " & "'" & stWho & "'"
    '-- Looks up email address from TBLACCDET
    varTo = DLookup("[ADEmail]", "TBLACCDET", "[ADPK]=" & MembID)

    stSubject = ":: Member Club Points Balance ::"

  '  stTicketID = Format(Me.txtTicketID, "00000")
  '  RecDate = Me.txtDateReceived
    '-- Helpdesk employee who assigns ticket
  '  strHelpDesk = Me.cboReceivedBy.Column(1)

    stText = "Your Club Points Balance is " & PointsAvailable & ". " & Chr$(13) & Chr$(13) & _
             "This is an automated message. Please do not respond to this e-mail."

    'Write the e-mail content for sending to assignee
    DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1

    'Set the update statement to disable command button
    'once e-mail is sent
  '  strSQL = "UPDATE tblHelpDeskTickets SET tblHelpDeskTickets.ysnTicketAssigned = -1 " & _
  '           "Where tblHelpDeskTickets.lngTicketID = " & Me.txtTicketID & ";"

'    On Error GoTo Err_Execute
'    CurrentDb.Execute strSQL, dbFailOnError
'    On Error GoTo 0

    'Requery checkbox to show checked
    'after update statement has ran
    'and disable send mail command button
 '   Me.chkTicketAssigned.Requery
 '   Me.chkTicketAssigned.SetFocus
 '   Me.cmdMailTicket.Enabled = False

    Exit Sub

'Err_Execute:

    ' Notify user of any errors that result from
    ' executing the query.
'    If DBEngine.Errors.Count > 0 Then
'        For Each errLoop In DBEngine.Errors
'            MsgBox "Error number: " & errLoop.Number & vbCr & _
'                   errLoop.Description
'        Next errLoop
'    End If

    Resume Next
    
Exit_CmdEmailPointsBalance_Click:
    Exit Sub

Err_CmdEmailPointsBalance_Click:
    MsgBox Err.Description
    Resume Exit_CmdEmailPointsBalance_Click

    

End Sub



I need a button in a form that will send an email marked as high priority with predefined text in the subject field. I put an email hyperlink in the form with the subject line, but now I've been asked to have the email marked as "high priority". The hyperlink thing was simple but I think I need to use code to mark it as high priority and unfortunately I don't know code. Can someone please help me?

Hello, first time posting with the site, so I hope I get this right.

I am building a form for entering data into a single table. I referred to another user's VBA code to check for multiple blank fields in the form by using the Other Tag property for the fields I want populated and then set the focus back to the first field in the form if there were any blanks.

The idea is to require the user entering data to enter data into these required fields. The code works, mostly, because it stops the record from being entered the first time thorugh. The problemm is that if the user tabs through or clicks the arrow to move to a new record on the form without changing or adding any data the second time, when the ok is clicked it adds the record to the table with the fields that I want populated as blank. I am using the following code on the Forms BeforeUpdate Event.

I would like to use this generalized code so that I can easily incorporate it into other forms that I will be designing in this same database.

Thanks in advance for any help you can offer!

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctr As Control
Dim strMsg As String
'Loop through every control on the form
For Each ctr In Me.Controls
'Look for a Particular Tag
If ctr.Tag = "BlkChk" Then
'Create a List of empty questions
If IsNull(ctr) Then
strMsg = strMsg & "_ " & ctr.Name & vbCrLf
End If
End If
Next ctr
'Did We Find Any Unanswered Questions?
If strMsg "" Then
If vbOK = MsgBox("The following fields require a response" & vbCrLf & vbCrLf & _
strMsg & vbCrLf & vbCrLf & "Do you want to proceed?", _
vbOKOnly) Then
Me.Work_Order__.SetFocus
End If
End If
End Sub

I've got a program with a lot of vba code, that when errors occur, generates and sends an e-mail informing the recipient of the error(s). Programmatically, everything works great. My "plight" is this: The user wishes to have the e-mail subject line bold-faced.

Generically, the code is:

Dim SubjErrMsg as string

SubjErrMsg = "There are errors present........."

followed by code that sets up the e-mail address, SubjErrMsg and message, and more code that then does the SendObj commands.

Does anyone know of how, codewise, I can boldface the subject line for sending?

Thank you....thank you...thank you!!!!


Hi,

How would i do this in VBA

I need the necessarry VBA code so that the lostfocus event of the email address, this last is checked whether it follows the pattern "myusername@myhome.com". An error will pop up othewise

thanks

Ut

Hi

I have a table with the following fields (amongst others)

LastName
FirstName
ZipCode

I want to check these fields when I am adding a new record through the form People so that having created a new record if the new records Last name, first name and Zip code match an existing record the user is prevented from adding a record.

Then if they only Lastname and firstname match a warning message comes up along the lines of

Somebody with this name already exists in the Database. Do you wish to create the record? Yes No

This would give the opportunity to cancel the creation and double check go on if the user was certain that the person did not already exist.

I already have the following to check for duplicate email but not all my contacts have email.

Private Sub EmailAddress_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String

SID = Me.EmailAddress.Value
stLinkCriteria = "[EmailAddress]=" & "'" & SID & "'"

'Check People table for duplicate email address
If DCount("EmailAddress", "People", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Email Address: " & SID & " has already exists in the database."
End If

End Sub

but I can not figure out from this how to check three fields together.

Can anybody help?

The data is held in a MS SQL database.

ps

I want to use a macro to do this. I am using the SendObject Method. On the subject line I already have Monthly Attendance Report. I would like it to say Monthly Attendance Report for John J Student. On the report I am using "=[FirstName] & " " & [MI] & " " & [LastName] & " " & [Suffix]" as the data source for the name.

How do I get it to do that?

Thank you.

HI, am pretty new at this and don't have any VBA training but I am develoing a 2007 MS Access database for folks at work and could use a sanity check on what I am doing.

The dB will produce many reports based on different division and branch workload. At present to send a report as a PDF file I create a macro to Send Object from MS Access ribbon. I choose Report, select report name, choose PDF format and add description for the subject line of the mail window. The macro works fine and produces the PDF file of each record as I like. In doing so I'm up to over 50 macros that send out reports as PDF files.

Question: I assume their is a much easier way. What am I doing wrong ?

Thank you for your response ! Art

Team,

I'll try to explain this as best I can. I want to put a button on my form that sends an email that pulls data from the current record i'm on.

Example: I'm on Mr. Smiths record. My button should send an email with
Mr. Smiths name in the subject line. His name is the data I want my email to pull.

So, when I click the button, my user who sent the late report should get an email that says (roughly)

To:
From:
Subject: (This is where I want the data from the record i'm on)

Body: Blah blah blah (name again) report is late blah blah blah

I am trying to create an Error Catch for a SendObject Command from a Form that resembles an Email Window.

I.E.
Email Address Line
Subject Line
Body Line

I have the Email Address Line Created from another form so the only error catch I need is when the Subject Line and or the Body Line are blank.

My Fields are of Course "Subject" for the Subject Line and "Body" for the Body Line

This is my program

Private Sub SendEmail_Click()

'CHECK TO SEE IF SUBJECT AND/OR BODY LINES ARE BLANK
If Subject = " " Then 'I have tried = 'is null' and '= Null' to no effect'
If Body = " " Then
DoCmd.SendObject acSendNoObject, , , BttmLn, , , , - 1
Else
DoCmd.SendObject acSendNoObject, , , BttmLn, , , , Body - 1
End If
Else
If Body = " " Then
DoCmd.SendObject acSendNoObject, , , BttmLn, , , Subject - 1
Else
DoCmd.SendObject acSendNoObject, , , BttmLn, , , Subject, Body - 1
End If
End If

End Sub

Thank you for any help on this matter.

Robert M

Is there a vba way to check for internet connection status before launching into procdures which assume a connection is present?

Matt

I am developing a database to be installed on computers used by let's say unsofisticated users who work for any of 20 some odd companies. They need to send an email back with a generated multi-sheet excel spreadsheet that I create through access. From all the MAPI and CDO posts I have seen my libraries don't have those objects or methods or I get the send using configuration value is invalid error. I do not know that they all have outlook and the .send method seems to just leave the message in the outbox anyway, making us rely on the user to hit send and receive. Which though simple might be too hard for these people. So I need some suggestions and if they rely on CDO or MAPI then I need a link to where I can download the library and how to register it. I am using access 2003 from an office enterprise edition.

Thank you in advance
Shannon

Dear All,

I am new to this forum and hoping, after many attempts to get the code right, to find someone with the required expertise.

I have a form with a button and 'on click' vba code for importing dbase files filtered by a parameter query. The routine to import works well, but only imports the dbf file(s) in the first directory path it comes to, even if there is more than one. The query (with date dialog parameter gathered from the form) filters and selects dbf files that exist in multiple folders. Each directory path is different and may contain many dbf files for import. I have a delete query that removes the data imported once it has been used. The following code works well but only imports the first dbf file in the first directory that the query selects, not the dbf files in the other directory paths selected by the query. How can I modify the code to get it to check all directory paths in the record set? Many thanks in advance for any help you can give.

Here is the code:
Private Sub cmdImport_Click()
On Error GoTo ErrHandler

Dim oFSystem As Object
Dim oFolder As Object
Dim oFile As Object
Dim sFile As String
Dim sFolderPath As String
Dim SQL As String
Dim i As Integer
Dim rst As Recordset


Set rst = CurrentDb.OpenRecordset("qryDBFilesForImport")

sFolderPath = rst("DBPath")
sFile = rst("DBFile")


Set oFSystem = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSystem.getfolder(sFolderPath)
Set oFile = oFSystem.getfile(sFile)

Do
Do Until sFolderPath = ""


For Each oFile In oFolder.files
If Right(oFile.Name, 4) = ".dbf" And oFile = sFile Then
SQL = "Insert into [tblDBFImportTemp]" _
& " Select """ & Left(oFile.Name, 7) & """ as [Key],*" _
& " from " & Left(oFile.Name, Len(oFile.Name) - 4) _
& " IN """ & sFolderPath & """ ""dBASE 5.0;"""

DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
i = i + 1

End If
Next




MsgBox i & "files imported", vbInformation, "Monitoring Database"

Exit Sub

ErrHandler:
MsgBox Err.Description
Loop
Loop
End Sub

I have code that successfully reads an open form for email subject, the reply to email address, the sender's name, the body of the email and the location of the attachment to be added. The following lines are in the code:


	Code:
	.Fields("urn:schemas:mailheader:disposition-notification-to") = sReplytoEmailAddr 
.Fields.update

In order to get non-delivery notifications I had to use a gmail account. It will only send the non-delivery notifications to the gmail address and not to sReplytoEmailAddr. I know sReplytoEmailAddr is in there correctly because if the email is successfully delivered, clicking "Reply to" pops up the sReplytoEmailAddr in the To field. Three people will be using this program, and each needs to get his own bounce notifications. What am I doing wrong and how do I fix it?

Currently writing database for my small business ( International Freight Forwarder/ Maritime Industry) I have made great progress after getting a few tips from users on this site so here is my next problem! I have a "edit Shipper Information Form" in which user can enter the below Data

Shipper
Address line 1
address line 2
city
state
zipcode
phone numbers etc..

My question pertains to the address. Each of the address fragments have their own field in table. On the main display page I'm combining them.

I managed to write the code for IF the second line of the address is empty then omit it, don't add that to the text box for full address display.

I had written this..

'' Combine address into full shipper address including both lines of street address
If IsNull(Form_AECLeditshipper.Shipper_Street_Address _2) = False Then
Form_AECLeditshipper.Shipper_Full_Address = (Form_AECLeditshipper.Shipper_Street_Address_1 + vbCrLf + Form_AECLeditshipper.Shipper_Street_Address_2 + vbCrLf + Form_AECLeditshipper.Shipper_City + " " + Form_AECLeditshipper.Shipper_State + ", " + Form_AECLeditshipper.Shipper_ZipCode + vbCrLf + Form_AECLeditshipper.Shipper_Country)
End If

'' Combine into full address if the second line of street address is null. Don't use Space in combination text box.
If IsNull(Form_AECLeditshipper.Shipper_Street_Address _2) = True Then
Form_AECLeditshipper.Shipper_Full_Address = (Form_AECLeditshipper.Shipper_Street_Address_1 + vbCrLf + Form_AECLeditshipper.Shipper_City + " " + Form_AECLeditshipper.Shipper_State + ", " + Form_AECLeditshipper.Shipper_ZipCode + vbCrLf + Form_AECLeditshipper.Shipper_Country)
End If



This works perfect!! Now.. on occasion in my industry i have a "foreign to Foreign" move in which there IS NO state or zip code..

Is there a way to check TWO text boxes or three for being empty..?? I had tried

If IsNull(Form_AECLeditshipper.Shipper_Street_Address _2 AND Form_AECLeditshipper.shipper_Zip_Code) = True Then

blah blah... It throws error.. Is there a way to check for the two text boxes not containga value? Then i can tell it how to format the "Full Address"


Not finding an answer? Try a Google search.