Saving a String as an html File

[Benjamin Salcetti]

I'm trying to save the body of an email sent out by a macro as an html
file on a shared drive. The body is mstrBody, already defined as a
string. The portion between the stars is what I've written in attempt
to save this, while the rest is part of a much longer macro. Stepping
through the code yields no problems, and running the entire set of
macros that governs this process will work fine, but nothing will be
saved. Could somebody help me figure out how to get this saved
properly?

Thanks

'Set the values for the email notification
With moEmailEng

.To = GetEMailAddressList(moTo)
.From = GetEMailAddressList(moFrom)

If GetEMailAddressList(moCC) <> "" Then
.CC = GetEMailAddressList(moCC)
End If

If GetEMailAddressList(moBCC) <> "" Then
.BCC = GetEMailAddressList(moBCC)
End If

'MsgBox "re-inserted stmts re: Attachments"

For intIndex = 1 To moAttachments.Count

If Trim(moAttachments(intIndex)) <> "" Then
.AddAttachment Trim(moAttachments(intIndex))
End If

Next intIndex

.Subject = mstrSubject
.HTMLBody = mstrBody

'*************************************************************************************************************************

'This next bit attempts to save the body as an html file on
the S Drive.

Dim UserName As String
Dim TimeInMS As String

UserName = Application.UserName
TimeInMS = Strings.Format(Now, "dd-MMM-yyyy HH:nn:ss") & "." &
Strings.Right(Strings.Format(Timer, "#0.00"), 2)

Dim F As Long
Dim FileName As String

FileName = "S:\Research\Shared\RATINGS MACROS\Archived Macro
Emails\" & UserName & "RatingsEmail" & TimeInMS & ".html"

F = FreeFile
Open FileName For Output As #F
Print #F, mstrBody
Close #F

'*************************************************************************************************************************

'Send Email -- THIS ACTUALLY SENDS EMAIL
.Send

End With

 

[Harald Staff]

Hi Benjamin

Usually in cases like this, putting
DoEvents
wherever system things happen usually helps. It means "wait and listen to
the operating system".

I'd start here:

F = FreeFile
Open FileName For Output As #F
DoEvents
Print #F, mstrBody
DoEvents
Close #F
DoEvents

'*************************************************************************************************************************

'Send Email -- THIS ACTUALLY SENDS EMAIL
.Send
DoEvents

HTH. Best wishes Harald

 

[Benjamin Salcetti]

Harald,

Thanks for the input. Stepping through the code, it still skips over
those last two lines, even with the DoEvents there (it ignores those
too). Is there a better way to set this up to save the html file?

Thanks

 

[Benjamin Salcetti]

I also tried the following, to no avail. Again, no errors with the
code, but nothing is saved.

Dim UserName As String
Dim TimeInMS As String

UserName = Application.UserName
TimeInMS = Strings.Format(Now, "dd-MMM-yyyy HH:nn:ss") & "." &
Strings.Right(Strings.Format(Timer, "#0.00"), 2)

Dim iFileName As String

iFileName = "S:\Research\Shared\RATINGS MACROS\Archived Macro
Emails\" & UserName & "RatingsEmail" & TimeInMS & ".html"

Dim FSO As Object '<--- FileSystemObject
Dim fsoTS As Object '<--- TextStream

Set FSO = CreateObject("Scripting.FileSystemObject")
Set fsoTS = FSO.CreateTextFile(FileName:=iFileName, _
Overwrite:=True)

With fsoTS
.HTMLBody = mstrBody
End With

 

[Harald Staff]

You must have some error handler somewhere, an On Error Resume Next og
something, that ignores runtime errors. Turn those off while debugging.

Colons are forbidden i filemanes, that would be one problem. Try saving as
some simple hardcoded standard name first, like "mailbody.html". If that
works then the filename is the problem.

And leave the DoEvents in there, they only do good.

Best wishes Harald

I also tried the following, to no avail. Again, no errors with the
code, but nothing is saved.

Dim UserName As String
Dim TimeInMS As String

UserName = Application.UserName
TimeInMS = Strings.Format(Now, "dd-MMM-yyyy HH:nn:ss") & "." &
Strings.Right(Strings.Format(Timer, "#0.00"), 2)

Dim iFileName As String

iFileName = "S:\Research\Shared\RATINGS MACROS\Archived Macro
Emails\" & UserName & "RatingsEmail" & TimeInMS & ".html"

Dim FSO As Object '<--- FileSystemObject
Dim fsoTS As Object '<--- TextStream

Set FSO = CreateObject("Scripting.FileSystemObject")
Set fsoTS = FSO.CreateTextFile(FileName:=iFileName, _
Overwrite:=True)

With fsoTS
.HTMLBody = mstrBody
End With

 

[Benjamin Salcetti]

Turns out the file name was the problem, thank you for your help.