Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    May 2008
    Posts
    18

    Unanswered: Insert field names into e mail

    I have written some code to send an e mail from a form. I am just wondering if I can insert a field result into the body of the e mail to help get more info out. At present the body just reads.

    .Body = "Above job has been raised for equipment allocation to go on hire please complete all item numbers and update record."

    For example it would help if I could insert the equipment needed.

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    .body = "Above job has been raised for equipment allocation to go on hire please complete all item numbers and update record." & nz(me.YourFieldNameOnForm,"") & " " & nz(me.YourOtherFieldNameOnForm,"")

    nz(nameoffield,"") - The NZ just caters for NULL values (no data).
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    May 2008
    Posts
    18
    garethdart, thanks that works like a charm and I am really making progress now to be more awkward can I make it recognise from a sub form? At the moment my code looks like

    .Body = "Job " & Nz(Me.ENQUIRY_NUMBER, "") & " has been raised for equipment allocation to go on hire" _
    & vbCrLf & "please complete all item numbers and update record." _
    & vbCrLf & "Equipment List" _
    & vbCrLf & Nz([Forms]![qryProduct sub form]![PRODUCT CODE], "")

    the e mail sends correctly but nothing appears where product code should be?

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Nz(Forms!frmYourMainFormName!YourSubformName![PRODUCT CODE],"")

    Should do the trick.

    For future reference when referring to controls on a subform, Keri Hardwick has a useful article "Refer to Form and Subform properties and controls"
    on Dev Ashish's TheAccessWeb listed here:

    http://www.mvps.org/access/forms/frm0031.htm

    __________________________________________________ ____________
    PS Try to avoid using spaces in field and database object names - It may help you to use a naming convention for all your database objects;

    Forms, Fields, Reports, VBA Variables - This can help you avoid some basic pitfalls like accidentally using a 'Reserved Word' and can make it easier for people to understand your work.

    One such convention is Leszynski/Reddick detailed: http://www.databasedev.co.uk/naming_conv.html
    Last edited by garethdart; 10-22-08 at 12:10.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    May 2008
    Posts
    18
    Again many thanks worked spot on, hopefully this will be the last question on this topic but how can I get it to loop the line to pick up all the records in the sub form relating to the main form?

    I am trying with my naming conventions unfortunately old habits die hard which is leading to a mixed bag and some weird and wonderful names

  6. #6
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    I don't know if you can easily loop through the subform, but you can open a recordset (either a query or SQL statement) with the same fields and criteria and loop through that.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    possibly the better approach would be to create a recordset which replicates the form/subform association, whether thats one recordset or an inner recordset is upto you, although a single recordset would make more sense to e, it maybe easier to develop it as different recordsets if you are not too familar with VBA & iterating through recordsets.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm a big fan of "tokenized" form letters/email/etc. It makes the design and debug a lot easier at the expense of some additional processing.

    I do something like this:

    "Dear [title], I am writing to tell you that I think you are [sentiment] and should consider [action] yourself. Regards [sender]"

    All of the tokens relate to field names, so I can then run through with replace and automagically knock it all out by feeding each column name in my record set in a loop, using something like this:

    Replace(FormText, "[" & rs(i).ColumnName & "]", rs(i))




    *Edit - Note that this is generic psuedo code. I haven't done that in standard VBA in some time so the syntax may be off.
    Last edited by Teddy; 10-23-08 at 16:23.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I like that approach Teddy, thanks
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    May 2008
    Posts
    18
    Thanks for all the replies and i believe i'm now at the final hurdle this code now works only problem being I can't set a loop in the body to move to the next equipment line, any ideas?

    Private Sub Command105_Click()
    Dim mbxresult As VbMsgBoxResult
    Dim psf As ADODB.Connection
    Set psf = CurrentProject.Connection
    Dim psfrec As New ADODB.Recordset
    psfrec.ActiveConnection = psf

    psfrec.Open "[Product Sub Form]", , adOpenForwardOnly, adLockReadOnly

    If Me.STATUS.Value = "HIRE REQUIRED" Then
    mbxresult = MsgBox("Please ensure all item numbers are entered and update status to ORDER", vbOKOnly)
    End If
    If Me.STATUS.Value = "ORDER" Then
    Dim Olk As Outlook.Application
    Set Olk = CreateObject("Outlook.Application")
    Dim OlkMsg As Outlook.MailItem
    Set OlkMsg = Olk.CreateItem(olMailItem)
    With OlkMsg
    Dim OlkRecip As Outlook.Recipient
    Set OlkRecip = .Recipients.Add("alan.radford@speedyhire.com")
    OlkRecip.TYPE = olTo
    .Subject = "New job ready to be raised as on hire"
    .Body = "Job " & Nz(Me.ENQUIRY_NUMBER, "") & " has been completed by the workshop and is ready to be raised as an order" _
    & vbCrLf & "On Hire Date: " & Nz(Me.ON_HIRE_DATE, "") _
    & vbCrLf & "Acc No: " & Nz(Me.[ACCOUNT NO], "") _
    & vbCrLf & "Acc Name: " & Nz(Me.ACC_NAME, "") _
    & vbCrLf & "Qty" & " " & "Product Code" & " " & "Description" _
    & vbCrLf & psfrec.Fields("QTY").Value & psfrec.Fields("PRODUCT CODE").Value & psfrec.Fields("DESCRIPTION M").Value _











    .Send
    End With
    Set Olk = Nothing
    Set OlkMsg = Nothing
    Set OlkRecip = Nothing

    DoCmd.Close acForm, "Workshop Number Form", acSaveYes
    End If



    DoCmd.OpenReport "Whiteboard Workshop", acViewPreview



    End Sub

  11. #11
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Here's a sample that loops through the results of a query to create a concantinated list. You should be able to tweek it to meet your needs. I have used this with tables, queries and SQL statements opened as recordsets, but never a form.

    jvRecCount = 0
    strSiteListMemo = ""
    Set rsSiteList = CurrentDb.OpenRecordset("qryQDValidSiteCodes")
    rsSiteList.MoveLast
    rsSiteList.MoveFirst


    For jvRecCount = 0 To rsSiteList.RecordCount - 1

    strSiteListMemo = strSiteListMemo & rsSiteList!SiteCode & ","



    rsSiteList.MoveNext
    Next jvRecCount
    rsSiteList.Close
    Set rsSiteList = Nothing

    Me.txtSiteCodeList = Left(strSiteListMemo, Len(strSiteListMemo) - 1)

  12. #12
    Join Date
    May 2008
    Posts
    18
    For jvRecCount = 0 To rsSiteList.RecordCount - 1

    What is this line doing?

  13. #13
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Counts the number of records to determine how many times to run through the loop. Count starts at 0 not 1.

  14. #14
    Join Date
    May 2008
    Posts
    18
    This doesn't give an error but also doesn't show anything in the e mail??

    psfcount = 0
    prodline = ""
    Dim psf As ADODB.Connection
    Set psf = CurrentProject.Connection
    Dim psfrec As New ADODB.Recordset
    psfrec.ActiveConnection = psf
    psfrec.Open "[Product Sub Form]", , adOpenForwardOnly, adLockReadOnly
    psfrec.MoveFirst


    For psfcount = 0 To psfrec.RecordCount - 1

    prodline = psfrec.Fields("QTY")

    psfrec.MoveNext
    Next psfcount

  15. #15
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Is Product Sub Form a form or query?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •