Send Email "Body Message.

[josros60]

Hi,

I have this code that i want send as body message in email but i want to pickup the company name according the company i am sending the invoice,

but giving me error 2465 cannot find the field,

Here it's the code:

Private Sub btnSend_Click()
    Dim oApp As New Outlook.Application
    Dim oEmail As Outlook.MailItem
    
    Set oEmail = oApp.CreateItem(olMailItem)
    
    oEmail.To = Me.txtTo
    oEmail.subject = Me.txtSubject
    oEmail.Body = "Here is the monthly invoice # for " & [Company Name] & "." & "Please confirm if we may collect the payment from your wallet.Please contact me if you have any questions.Thanks,"

when give me the above error and click debug it highlight this line:

"Here is the monthly invoice # for " & [Company Name] & "." & "Please confirm if we may collect the payment from your wallet.Please contact me if you have any questions.Thanks,"

thanks

 

[pbaldy]

Presuming it's on the form, I'd use:

Me.TextboxName

 

[Minty]

As Paul stated - You can't refer to [Company Name] in the middle of the Outlook Object Body reference.

Set it in a string before hand and refer to the string, something like

Dim sBody as String

sBody = "Here is the monthly invoice # for " & [Company Name] & "." & "Please confirm if we may collect the payment from your wallet.Please contact me if you have any questions.Thanks,"

Debug.Print sBody       [COLOR="Green"] 'This will show you what is pulled in in the immediate window[/COLOR]

oEmail.Body = sBody

 

[josros60]

Thank you. I tried the suggested code below, but it gets me the id number instead of company name, how can i make to get the Customer name instead.

Private Sub btnSend_Click()
    Dim oApp As New Outlook.Application
    Dim oEmail As Outlook.MailItem
    Dim sBody As String
    Set oEmail = oApp.CreateItem(olMailItem)
    
    oEmail.To = Me.txtTo
    oEmail.subject = Me.txtSubject
    sBody = "Here is the monthly invoice # for " & Me.txtCompany & "." & "Please confirm if we may collect the payment from your wallet.Please contact me if you have any questions.Thanks,"
    'oEmail.Body = "Here is the monthly invoice # for " & [Company Name] & "." & "Please confirm if we may collect the payment from your wallet.Please contact me if you have any questions.Thanks,"
    
       
    If Len(Me.txtAttachment) > 0 Then
        oEmail.Attachments.Add Me.txtAttachment.Value
    End If
    With oEmail
      '  If Not IsNull(.To) And Not IsNull(.subject) And Not IsNull(.Body) Then
          .Display
            MsgBox "Remember to attach Invoice(s)"
       End With
      
   oEmail.Body = sBody
   Debug.Print sBody
End Sub

thank you very much.

 

[pbaldy]

I suspect you've got a lookup field? Is txtCompany a textbox or a combo? If it's a combo, try referring to the column with name:

Me.txtCompany.Column(x)

 

[josros60]

it's a combo, how do i know the column number?

thanks

 

[pbaldy]

Look at the row source. The first column displayed is 0, second is 1, etc.

 

[josros60]

Thanks so much, it worked.

 

[pbaldy]

No problem.

 

[josros60]

Sorry, i have one more question if you please can help me,

depending on the customer, some of them already approved us to withdraw from their wallet, and other one has to confirm

how can i make that to work,

here are the two messages:

1.

="Here is the monthly invoice # for " & Me.txtCompany.Column(1) Please confirm if we may collect the payment from your wallet." & vbCrLf & vbCrLf & "Please contact me if you have any questions.Thanks,"

2.

="Here is the monthly invoice # for " & Me.txtCompany.Column(1) As per your instructions we will withdraw the amount from your loading wallet. " & vbCrLf & vbCrLf & "Please contact me if you have any questions.Thanks,"

i hope you can give me an idea how to make this work,

thanks

 

[pbaldy]

How do you know which is which? Presuming a field on the form, use an If/Then/Else to add the appropriate text.

 

[josros60]

Because this switching from excel to Access and we have a list of customer that approve to withdraw, so in excel type message depending on the customer.


how can i use an If/Then/Else to add the appropriate text.

any idea?

 

[pbaldy]

If WhateverTheAppropriateTestIs Then
  sBody = "Here is one string"
Else
  sBody = "Here is the other"
End If

 

[josros60]

Re: Send Email "Body Message. (SOLVED)

Thank you for all your help.

everything works.

 

[pbaldy]

Happy to help!