Stumped (1 Viewer)

[NearImpossible]

I have a bit of code that searches through some records and generates emails based off of the results, but for some reason, when the email is generated, it is adding a carriage return or something that is making txt drop to the next line and I can't seem to figure it out.

Here is the bit of code that is giving me issues

     'Opens and starts email
          Set oOutlook = CreateObject("Outlook.application")
          Set oEmailItem = oOutlook.CreateItem(olMailItem)
               With oEmailItem
                    .SentOnBehalfOfName = "SENDER"
                    .To = "RECIPIENT"
                    .Subject = fac & " Scheduled Maintenance on " & mdy & " " & Format(DateAdd("d", (nos), Date), "mm/dd") & " at " & mtm
                    .HTMLBody = nmt  & " " & mdy & " " & Format(DateAdd("d", (nos), Date), "mm/dd") & " at " & mtm &"."& "<br><br>" & nms
                     .display
                    ' .Send
               End With

Result of nmt is This is a reminder that your Server and Kiosk maintenance is scheduled for
Result of mday is Friday
Result of Format(DateAdd("d", (nos), Date), "mm/dd") is 07/17
Result of mtm is 11:00:00 AM CST
Result of nms is
The maintenance typically takes about 30 minutes, but we like to scheduled 1 hour in case we run into any issues. We will reach out prior to rebooting the server as your program will not be available while it is rebooting.

If you need to reschedule, please reply to this email and let us know

Thank You


and the result of the " nmt & " " & mdy & " " & Format(DateAdd("d", (nos), Date), "mm/dd") & " at " & mtm &"."& " " is being split on 2 lines instead of 1 so the email reads as follows

This is a reminder that your Server and Kiosk maintenance is scheduled for
Friday 07/17 at 11:00:00 AM CST.

The maintenance typically takes about 30 minutes, but we like to scheduled 1 hour in case we run into any issues. We will reach out prior to rebooting the server as your program will not be available while it is rebooting.

If you need to reschedule, please reply to this email and let us know

Thank You

I have verified that I don't have any extra lines in any of the fields in my tables

 

[Isaac]

What about your code specifying
<br><br>

 

[NearImpossible]

What about your code specifying
<br><br>

That works just fine as it should so the "nms" content is the next line down, however the beginning content should only be on 1 line.

This is a reminder that your Server and Kiosk maintenance is scheduled for Friday 07/17 at 11:00:00 AM CST.

The maintenance typically takes about 30 minutes, but we like to scheduled 1 hour in case we run into any issues. We will reach out prior to rebooting the server as your program will not be available while it is rebooting.

If you need to reschedule, please reply to this email and let us know

Thank You


and not

This is a reminder that your Server and Kiosk maintenance is scheduled for
Friday 07/17 at 11:00:00 AM CST.

The maintenance typically takes about 30 minutes, but we like to scheduled 1 hour in case we run into any issues. We will reach out prior to rebooting the server as your program will not be available while it is rebooting.

If you need to reschedule, please reply to this email and let us know

Thank You

 

[NearImpossible]

If I change the fields from "Rich Text" to "Plain Text" the issue goes away as its only 1 line, but if I try to add any additional lines to the nmt field, it still combines it all as 1 line.

Its almost like the Rich Text and Plain Text fields are broken

 

[Isaac]

I see.
I think we would need to see the rest of your code that hasn't been posted yet. You are referencing a number of variables, and the problem may be there.

 

[NearImpossible]

Here is the full code

Private Sub MxEmail_Click()

Dim srs As Recordset        'Schedule Records
Dim ers As Recordset        'Email Recordset
Dim drs As Recordset        'Maintenance Date Recordset

Dim smx As String           'Scheduled Maintenance day
Dim mdy As String           'Maintenance Day of week
Dim mtm As String           'Maintenance Time
Dim nos As String           'Days prior to MX day to send notification
Dim fac As String           'Facility
Dim eml As String           'Email Address
Dim emd As String           'Email Day
Dim nmt As String           'Notificiation Message Header
Dim nms As String           'Notificiation Message
Dim udf As String           'Update Frequency
Dim fmd As String           'First Maintenance Date
Dim fid As String           'Facility ID
          
Dim oOutlook As Object      'Outlook
Dim oEmailItem As Object    'Email

    mxd = TodayIsNthDay(Date)
  
  
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "FacilityMXEmails"  'Builds Email List for all facilities due within the next 15 days
    DoCmd.OpenQuery "Update54FacilityMXEmail Query"     'Updates from the "5th" to the "4th" if the month doesn't have 5 weeks in it
  
    'Reads the FaxilityMxEmail table to find any facility maintenance emails that need to be sent out
    Set srs = CurrentDb.OpenRecordset("Select * from [FacilityMXEmail] where [Esent] = 0", dbOpenDynaset, dbSeeChanges)
        While Not srs.EOF
            smx = ""
            nos = ""
            emd = ""
            fac = ""
            smx = smx & srs("MxWeek") & " " & srs("MxDay")
            nos = nos & srs("Notification Days")
            emd = TodayIsNthDay(DateAdd("d", (nos), Date))

            If emd = smx Then
                eml = ""
                mdy = ""
                mtm = ""
                nms = ""
                nmt = ""
                fid = ""
                fac = fac & srs("Facility Name")
  
                'Builds the Email Address List for applicable facilities
                Set ers = CurrentDb.OpenRecordset("Select * from [FacilityMXEmail] where [Facility Name] ='" & fac & "'", dbOpenDynaset, dbSeeChanges)
                    While Not ers.EOF
                        eml = eml & ers("[Email]") & ";"
                        With ers
                            .Edit
                            .Fields("Esent") = True
                            .Update
                        End With
                        ers.MoveNext
                    Wend
                  
                nmt = nmt & srs("Notification Header")
                nms = nms & srs("Notification Message")
                mdy = mday & srs("MxDay")
                mtm = mtm & srs("FinalDay")
                fid = fid & srs("FacilityID")
              
                On Error GoTo err
                'Opens and starts email
                    Set oOutlook = CreateObject("Outlook.application")
                    Set oEmailItem = oOutlook.CreateItem(olMailItem)
                    With oEmailItem
                        .SentOnBehalfOfName = "SENDER"
                        .To = eml
                        .Subject = fac & " Scheduled Maintenance on " & mdy & " " & Format(DateAdd("d", (nos), Date), "mm/dd") & " at " & mtm
                        .HTMLBody = nmt & " " & mdy & " " & Format(DateAdd("d", (nos), Date), "mm/dd") & " at " & mtm & "." & "<br><br>" & nms
                        .display
                    '   .Send
                    End With
                      
                'Checks & Updates Maintenance Dates
                    Set drs = CurrentDb.OpenRecordset("select * from [dbo_FacilityMxFrequency] where FacilityID =" & fid & "", dbOpenDynaset, dbSeeChanges)
                        udf = ""
                        fmd = ""
                          
                        udf = udf & drs("[UpdateFrequency]")
                        fmd = fmd & drs("[First Maintenance]")
                    With drs
                        If udf = "LD Only - Every 6 Months" And fmd <> "" Or udf = "LD & Lobby Only - Every 6 Months" And fmd <> "" Then
                            .Edit
                            .Fields("First Maintenance") = DateAdd("M", 6, fmd)   'Updates next maintenance date to 6 Months from today
                            .Update
                        ElseIf udf = "Windows & LD Only - Monthly" And fmd <> "" Or udf = "Windows, LD & Lobby - Monthly" And fmd <> "" Then
                            .Edit
                            .Fields("First Maintenance") = DateAdd("M", 1, fmd)   'Updates next maintenance date to 1 Month from today
                            .Update
                        End If
                    End With
            End If
            srs.MoveNext
        Wend
      
        MsgBox "All Emails generated"
              
        'Clears Email settings
            Set oEmailItem = Nothing
            Set oOutlook = Nothing
          
    Exit Sub
  
err:
    MsgBox "Email is not setup on this PC"
    Exit Sub

End Sub

 

[Isaac]

Thanks for posting. The variables are really hard to follow, but I think what it comes down to is also the possibility that you have a linebreak in your recordset values OR variables/function returns.
One idea to check is to run Select * from [FacilityMXEmail] with a column:
LineBreakTest10: Instr(1,[fieldname],chr(10))
and
LineBreakTest13: Instr(1,[fieldname],chr(13))

and use >0 for the criteria on each. Unfortunately you'd have to do this on any fields suspected or even possible to have a line break.

Another method of accomplishing this would be to set a Breakpoint on the .htmlbody line and View > Immediate Window, and type
?Instr(1,VariableName,Chr(10)) [then press Enter]
I guess this would actually be more effective, because your code references other functions, too, that are acting on things, and checking each variable immediately prior to HTMLBody would cover all possibilities--whether a recordset field value had a line break, or whether any variable got a line break through earlier code.

And do that for each and every variable about to be used in the html body. That will narrow it down.

 

[NearImpossible]

Another method of accomplishing this would be to set a Breakpoint on the .htmlbody line and View > Immediate Window, and type
?Instr(1,VariableName,Chr(10)) [then press Enter]
I guess this would actually be more effective, because your code references other functions, too, that are acting on things, and checking each variable immediately prior to HTMLBody would cover all possibilities--whether a recordset field value had a line break, or whether any variable got a line break through earlier code.

And do that for each and every variable about to be used in the html body. That will narrow it down.

all came back with 0 for both 10 and 13

 

[Isaac]

Is there any way you can post your database with some data in it? Even if it was just one record in the source table as long as it still produces that problem.

 

[NearImpossible]

so while copying everything over to a local database I noticed <DIV> tags on the string in question <DIV>This is a reminder that your Server and Kiosk maintenance is scheduled for </DIV>

I removed the DIV tags and it works how its supposed to

The form that field is entered on is a Rich Text field and the first time it is ever opened, it auto populates with the above text and the email works. If I change the message in anyway, it then comes across with the DIV tags and is causing the line to split in two so I am guessing that is where the issue is

 

[Isaac]

that definitely sounds like it was the issue then. I'm glad you got it sorted out. I haven't looked at your posted database since it sounds like you found the problem, but if there is anything else I can look at to be helpful let me know. Good continued success in your project!