Emailing from form 1

[air1jcw]

I have a code that I am working with that is really neat, and works great. I just don know how to get rid of Error.number 94. I want the code to be able to ignore a field if it is null. Here is the code I am working with:
I am having troubles with the Command101_Err. I what have thus far, cuts part of the string when it is place on a new email message.

Thank you in advance!!!

Air.

Private Sub Command101_Click()
On Error GoTo Command101_Err

Dim strEmail, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application&quot
Set objEmail = objOutlook.CreateItem(olMailItem)

'*create string with email address
strEmail = LMPers

strBody = strBody & "ATTN: " & UCase(CStr(Forms!WWRDataEntry!ATTN)) & " " & Chr(13) & Chr(13)
strBody = strBody & "Your station has been affected by a World Wide Review of IID " & IID1 & ", " & UCase(CStr(Forms!WWRDataEntry!FleetType)) & ", " & UCase(CStr(Forms!WWRDataEntry!Nomenclature)) & " , which includes P/N(s) " & UCase(CStr(Forms!WWRDataEntry!PN)) & ". The following allocations changes have occured:" & Chr(13) & Chr(13)
strBody = strBody & "**DEALLOCATIONS: " & UCase(CStr(Forms!WWRDataEntry!GTWY1)) & " - Due to: " & UCase(CStr(Forms!WWRDataEntry!DeAllocationReason)) & " (Please return SV CPW)" & Chr(13) & Chr(13)
strBody = strBody & "**OVERALLOCATIONS: " & UCase(CStr(Forms!WWRDataEntry!GTWY2)) & " (Please return SV CPW)" & Chr(13) & Chr(13)
strBody = strBody & "**NEW ALLOCATIONS: " & UCase(CStr(Forms!WWRDataEntry!GTWY3)) & " (Please be advised)" & Chr(13) & Chr(13)
strBody = strBody & "**WORLDWIDE ALLOCATIONS: " & UCase(CStr(Forms!WWRDataEntry!TotalAllocations)) & " - Backorders will occur until all de-allocated/over-allocated and/or newly purchased units are recieved. Please FWD this to any interested parties." & Chr(13) & Chr(13)
strBody = strBody & "**SUMMARY: This is a " & UCase(CStr(Forms!WWRDataEntry!Summary)) & ". The current worldwide allocation investment for IID " & IID1 & " is " & TotalCost & ". We feel that these changes will provide the most complete coverage by maintaining the best possible utilization of our inventory assest." & Chr(13) & Chr(13)
strBody = strBody & "**CONTACT: Feel free to contact LMP via email address " & UCase(CStr(Forms!WWRDataEntry!emailcontact)) & " atlas 5-350-" & ATLAS & " and/or 502 - " & PHONE & " with any questions or concerns. Thank you for your cooperation with all material movements." & Chr(13) & Chr(13)
strBody = strBody & "Regards," & Chr(13) & Chr(13)

'***creates email
With objEmail
.TO = strEmail
.Subject = "WWR P/N " & PN & " " & UCase(CStr(Forms!WWRDataEntry!Nomenclature)) & ""
.Body = strBody
.Display
End With

Command101_Err:
'If a field on the form is empty - continue.
If strBody = strBody & Null Then
strBody = ""
Resume Next

End If


Set objEmail = Nothing

Exit Sub

End Sub

 

[jrbarnett]

Hi,

There are a couple of ways of approaching your problem:

The first is to put "If IsNull()... End If " traps around where fields are put in, you then get the option to put something else in if the field is null.

The second is to rewrite your error handler so that error 94 (Invalid use of Null) will resume at the next line.

Something like:

Command101_Err:
  Select Case Err.Number
     Case 94 ' Invalid use of Null
        Resume Next
     Case Else
        MsgBox "Error " & Err.Number & ": " & Err.Description
        Resume Next
  End Select

You then need to put an "On Error Goto Command101_Err" line just before the Outlook object is instantiated to call the error handler, and put an Exit Sub line after the end of the End With, or your error hander will get called after a successful run.

John

 

[air1jcw]

jrbarnett,

I have tried using your examples. Thank you for the help. What happens is when a field on the form is null - the code cuts out that entire string. I can't have this happen. I need the string to be present in the email wether the field is null or not.

Is there a way to to do this with a null value? I don't know exactly how to put it into the code for each field. I will more then likely need for you the "spell" it out for me!!

One more thing while we are at it: Is there a way to make certain parts of the sting bold? Like the words "DEALLOCATIONS" or "OVERALLOCATIONS". I also would like for the outputted fields on to the email be in bold.

Thank you very much for you help!!

air

 

[jrbarnett]

Hi,

Take this as an example:

strBody = strBody & "ATTN: " & UCase(CStr(Nz(Forms!WWRDataEntry!ATTN), "&quot) & " " & Chr(13) & Chr(13)

as one way of approaching the problem. This will replace the field with an empty string if it is null, or slightly more complex, but easier to understand:

If IsNull (Forms!WWRDataEntry!ATTN) Then
  strBody = strBody & "ATTN: " & UCase(CStr(Forms!
  WWRDataEntry!ATTN)) & " " & Chr(13) & Chr(13)
Else
  strBody = strBody & "ATTN: " & Chr(13) & Chr(13)
End If

As for the items turning bold (or other enhanced formatting), for this you would need to use HTML format or Rich Text format email, neither of which are 100% portable across email clients (ie not all email clients can read them properly), so I would be hesitant to do this unless you are sure that they are using something in which this can be viewed properly.

John

 

[air1jcw]

jrbarnett,

AWESOME!!! What you provide has helped!!! Thank you very much.

As far as the text turning bold... This email is used internally throughout our office, and I know that everybody is using the same email format. If you could supply the code for making the text bold - that would be the "hootinanny"!!!

How do you get the email message to open with the users default email signature? This is another thing that we are all using.

Once again - Thank you ever so kindly!!

air

 

[jrbarnett]

Air,

To generate HTML format email, what you basically have to do is edit the strBody variable so that it contains embedded HTML tags to include the appropriate formatting. You will also need to ensure that your Outlook or Outlook express email clients are configured to permit the sending of HTML email. The actual HTML tag to turn on bold display is <b> and to switch it off is </b>, but you will need far more code than that.

Because of the way it works, you can't just write the parts you want in HTML, you have to convert the whole message. Nor can you just use your favourite web page editing package to generate the HTML code and copy it across.
If you are not familiar with writing raw HTML, I strongly suggest that you look at the FAQs in the HTML and CSS forum here: forum215 for setting the signature, I am not sure how to do this. I don't use Microsoft email clients for security reasons, but think it must be something like olMail.Signature = &quot;SignatureName&quot; or an index number.

Also I have spotted a mistake in my previous post. It should read:
UCase(CStr(Nz(Forms!WWRDataEntry!ATTN, &quot;&quot))

John