If for example D2 is empty (column C has previous mileage, column D has current mileage), the formula would cause "ERROR, mileage not reported" to be displayed in E2. The problem is...only the message is what shows up in the email recipient's attachment (column E) and although it is still an accurate message for the recipient to see, I want the formula to also be there because the recipient will be asked to enter their mileage into the spreadsheet (and return to us) and the formula is supposed to alert them with the messages if the mileage they enter is wrong in some way.
Here is the section of my code that does the sending. Was wondering if my problem had to do with the CreateRichTextItem of > Set obAttachment = noDocument.CreateRichTextItem("stAttachment"). I'm a novice at vba code..so any help/direction would be very much appreciated.Public Sub SendEmail(ByVal pEmail As String, ByVal pEFN As String, ByVal pNotifType As String, pNotification As String, ByVal pPOC As String) 'Inherits System.Windows.Forms.RichTextBox Dim noSession As Object, noDatabase As Object, noDocument As Object Dim obAttachment As Object, EmbedObject As Object Dim stSubject As Variant, stAttachment As String Dim vaRecipient As Variant, vaMsg As Variant Dim Password As String Dim CurDate CurDate = Date Dim LDate As String LDate = (MonthName(DatePart("m", CurDate))) & ", " & DatePart("yyyy", CurDate) Const EMBED_ATTACHMENT As Long = 1454 Const stTitle As String = "Active workbook status"x Const stMsg As String = "The active workbook must first be saved " & vbCrLf _ & "before it can be sent as an attachment." Do vaRecipient = pEmail Loop While vaRecipient = "" 'If the user has canceled the operation. If vaRecipient = False Then Exit Sub If pNotification = "Final mileage due to Govt" Then vaMsg = "Removed for the message board " & LDate & vbCrLf & vbCrLf ElseIf pNotification = "Gas cutoff notification" Then vaMsg = "Removed for the message board" & vbCrLf & vbCrLf ElseIf pNotification = "Third notice" And pNotifType <> "PM service due notification" Then vaMsg = "Removed for the message board, " & vbCrLf & vbCrLf _ Else 'First and second notices (third notice is above) Do vaMsg = "Removed for the message board, " & vbCrLf & vbCrLf _ Loop While vaMsg = "" End If 'If the user has canceled the operation. If vaMsg = False Then Exit Sub Do stSubject = pEFN Loop While stSubject = "" stAttachment = ActiveWorkbook.FullName 'Instantiate the Lotus Notes COM's Objects. Set noSession = CreateObject("Notes.NotesSession") Set noDatabase = noSession.GETDATABASE("", "") 'If Lotus Notes is not open then open the mail-part of it. If noDatabase.IsOpen = False Then noDatabase.OPENMAIL 'Create the e-mail and the attachment. Set noDocument = noDatabase.CreateDocument Set obAttachment = noDocument.CreateRichTextItem("stAttachment") Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment) 'Add values to the created e-mail main properties. With noDocument .Form = "Memo" .SendTo = vaRecipient .Subject = stSubject .Body = vaMsg .SaveMessageOnSend = True End With 'Send the e-mail. With noDocument .PostedDate = Now() On Error GoTo Err_Handle .Send 0, vaRecipient 'Release objects from the memory. Set EmbedObject = Nothing Set obAttachment = Nothing Set noDocument = Nothing Set noDatabase = Nothing Set noSession = Nothing Exit Sub Err_Handle: If vaRecipient = " " Then MsgBox pPOC & " has no email address in column L of your Master spreadsheet" Else MsgBox " Either the Group is setup wrong in this program for " & pPOC & " or something else is going on. Alert Catherine" End If End With 'Release objects from the memory. Set EmbedObject = Nothing Set obAttachment = Nothing Set noDocument = Nothing Set noDatabase = Nothing Set noSession = Nothing End Sub
The above formula does not work, but I dont know why. I want it to lookup the city tax and add it in if the customer is not on the tax exempt list.