Email Address From Access From

[JohnPeasley]

I'm using an Access form to populate an Outlook email template. I have been successful getting the data from the form into an email using this sequence Body = Replace(Body, "@1", Me.[BUREAU CODE]) with a control source tied to a specific table or query. My problem is one of my form fields uses =dlookup to populate the form with the person's email address based on various criteria. Because of that, I don’t know how to tell Access to take that field and put it in the email To field, since I don't have a specific Access field name like [Bureau Code] to use in my "Replace" sequence.

 

[Uncle Gizmo]

Do you mean that the Field on the form is unbound in other words it's not linked to an underlying table? If that's the case then you could probably Direct your VBA code to the actual text box on the form something similar to this method:- https://youtu.be/zAED0N31sm0?t=4m41s

 

[pbaldy]

Both posts were moderated, I've approved them.

 

[Uncle Gizmo]

Yes, I noticed that, but couldn't see how to "unmoderate" them?

Sent from my SM-G925F using Tapatalk

 

[isladogs]

I sent a couple of posts recently where I tried including screenshot images as part of the post.. both were sent for moderation. Neither have since appeared.

I'm not sure if those are the ones you both mentioned

Either way, whilst I totally understand the need for moderation, I'm not clear why my messages were lost...

In the meantime, I've given up on 'inline' images in posts

 

[Gasman]

No need to replace for those items.
The message has them as properties?

Below is how I send an email from Access in my charity database.
I actually use the names in my outlook contacts rather than hard coding them. That way if a caseworker changes their email (and they have) it still works?

        ' Create the message if first time we are in a different client or tran type.
        Set objOutlookMsg = objOutlook.CreateItemFromTemplate(strTemplatePath & "\SSAFA Email.oft")
        
        With objOutlookMsg
            ' Set the category
            .Categories = "SSAFA"
            .Importance = olImportanceHigh
            ' Add the To recipient(s) to the message.
            Set objOutlookRecip = .Recipients.Add("Jim Needs - SSAFA Swansea")
            objOutlookRecip.Type = olTo
    
            ' Add the CC recipient(s) to the message.
            If rs!CCOffice Then
                Set objOutlookRecip = .Recipients.Add("** SSAFA West Glamorgan Branch")
                objOutlookRecip.Type = olCC
            End If
            
            ' Need to get the Case Worker name from table'
            If rs!CaseWorker > 0 Then
                rsCW.FindFirst "[ID] = " & rs!CaseWorker
                strCaseWorker = rsCW!Data
            Else
                strCaseWorker = ""
            End If

            If strCaseWorker <> "" Then
                Set objOutlookRecip = .Recipients.Add(strCaseWorker)
                objOutlookRecip.Type = olCC
            End If
    
            ' Set the Format, Subject, Body, and Importance of the message.
            '.BodyFormat = olFormatHTML
            strClient = rs!Client


            If strType = "Payment" Then
                .Subject = " Payment Made - " & strClient
            Else
                .Subject = "Deposit Received - " & strClient
            End If
            ' Now start the email with header
            'iColon = InStr(strClient, ":")
           ' If iColon = 0 Then iColon = Len(strClient) + 1
            .HTMLBody = strHeader & "<table><tr>"
            '    .HTMLBody = .HTMLBody & "<td>" & "Client: " & strPadCol & Left(strClient, iColon - 1) & strEndPad
            'End If

        End With

I'm using an Access form to populate an Outlook email template. I have been successful getting the data from the form into an email using this sequence Body = Replace(Body, "@1", Me.[BUREAU CODE]) with a control source tied to a specific table or query. My problem is one of my form fields uses =dlookup to populate the form with the person's email address based on various criteria. Because of that, I don’t know how to tell Access to take that field and put it in the email To field, since I don't have a specific Access field name like [Bureau Code] to use in my "Replace" sequence.

 

[pbaldy]

Yes, I noticed that, but couldn't see how to "unmoderate" them?

Sent from my SM-G925F using Tapatalk

Select post(s), click on moderation dropdown at bottom of thread and select approve posts.

 

[pbaldy]

I sent a couple of posts recently where I tried including screenshot images as part of the post.. both were sent for moderation. Neither have since appeared.

I'm not sure if those are the ones you both mentioned

Either way, whilst I totally understand the need for moderation, I'm not clear why my messages were lost...

In the meantime, I've given up on 'inline' images in posts

They were in the code repository, which is a moderated forum. I've been out of town, though another moderator should have seen and dealt with them. I've moved them to an unmoderated forum:

https://www.access-programmers.co.uk/forums/showthread.php?t=293076

 

[isladogs]

Hi Paul

Thanks for that.
I've checked them & it seems I failed to do images within the post anyway though I followed the instructions elsewhere in the forum

The messages are out of date now so they can be deleted.
Not sure if I can do that without deleting the rest of each thread.

However if I had wanted to move them elsewhere on the forum, how could I do so from that location.

Also I'm going to start uploading code & sample databases soon. Are you saying each of these will need moderating first?

Thanks

Colin

 

[pbaldy]

You have enough posts that they should not be moderated anymore. Those last 2 got moderated because of the forum you put them in. I think anything posted to a forum in the "Microsoft Access Reference" section requires moderation. Just post to one of the regular forums and you should be fine.

 

[JohnPeasley]

Gasman: I appreciate your response! I’m not sure it quite meets my problem either that or I may be misunderstanding your code. I’m using DLookUp to find my email address Email1 from the table Contacts, based on matching the Allotment Code on the form to the Allotment number in the Contacts table. This is what I am using to look up the individual’s email address to enter it on the form: =DLookUp("Email1","[Contacts]","[Allotment]=" & [Allotment Code]). My problem is I can’t figure out what I need to write to get the email address into the "To" field in my email.

Note: I have brackets around Email1, but the system wouldn't let me post it thinking it was an email address

 

[Gasman]

Unless I am misunderstanding you.
The message has properties much like a control has.
So in the code I posted I set the .To of the email from a hard coded value "Jim Needs". That is just so it searches my outlook address book and gets the current email address.

You just need to put your variables in the properties of the message.

You are already doing it with .Body ?

So the code would be something along the lines of

            Set objOutlookRecip = .Recipients.Add(Me.EmailAddress)   
           objOutlookRecip.Type = olTo

Where Me.EmailAddress is the control on your form?

Thst is just one syntax.
In another DB I am doing as below, which is perhaps eaiser to read. It is just that most of my code is gleaned through googling and modifying, or being shown a method here.

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
   ' Change the mail address and subject in the macro before you run it.
    With OutMail
        .Display
    End With
    With OutMail
        .To = pStrToNames
        .cc = "yyy@xxxx.co.uk"
        .BCC = ""
        .Subject = pstrSubject
        .htmlbody = Replace(.htmlbody, strDiv, strDiv & strBody)
        '.attachments.Add pstrFilename & ".pdf"
        .attachments.Add pstrFilename
        .Send
    End With

The reason they are different is the first was created at home, and the second in work. As I did not have the code to hand in work I would have googled again for a method, and that just happened to be it.?

Any clearer?

 

[JohnPeasley]

The problem is solved thanks to several of the posts above, which gave me hints.

I can't believe how simple it was. I just added another line: objOutlookMsg.To = Me.[Text113]

Thanks Again