none
Add text to a specific location in body of email using VBA outlook RRS feed

  • Question

  • Ok, I  have been scouring the forums and google for a solution and I'm not having much luck.  Most of what I have so far is working, thanks to the help of several forum contributors.   I have a macro that creates a new mail item from an .rft file.  I am using .rft because the attachments need to be in the body of the email at specific locations.  I am checking if a file exists and if it does, I attach it at a given character count position.  However, what I can't figure out is how to add text if the file doesn't exist.  I can add the text to the end of the email by joining .body & "text", but I need the text to insert where the file would have inserted if it had existed.  Once that's done, I want the macro to continue to the next file attachment, and then the next and so on until it finally opens the email for me to review.

    Here is what I have so far.

    Option Explicit
    Function FileThere(FileName As String) As Boolean
        FileThere = (Dir(FileName) > "")
    End Function

    Sub CreateFromTemplate()
        Dim myOlApp As Outlook.Application
        Dim myItem As Outlook.MailItem
        Dim myAttachments As Outlook.Attachments
        Dim NewText As String
        
        Set myOlApp = CreateObject("Outlook.Application")
        Set myItem = myOlApp.CreateItemFromTemplate("M:\user\tma\server\Event Log.oft")
        Set myAttachments = myItem.Attachments
        
        With myItem
            .subject = "Event Log " & Date
            If FileThere("\\Insite\DRIVE-C\WINDOWS\system32\LogFiles\SMTPSVC1\ex" & Format(Now() - 1, "yymmdd") & ".log") Then
              myAttachments.Add "\\Insite\DRIVE-C\WINDOWS\system32\LogFiles\SMTPSVC1\ex" & Format(Now() - 1, "yymmdd") & ".log", _
              olByValue, 604, "Insite Log"
            Else: .body = .body & "No Log File"
            End If
            If FileThere("\\Internet\DRIVE-C\WINDOWS\system32\LogFiles\MSFTPSVC1\ex" & Format(Now() - 1, "yymmdd") & ".log") Then
              myAttachments.Add "\\Internet\DRIVE-C\WINDOWS\system32\LogFiles\MSFTPSVC1\ex" & Format(Now() - 1, "yymmdd") & ".log", _
              olByValue, 604, "Insite Log"
            Else
            Else: .body = .body & "No Log File"
            End If
            myItem.Display
         End With
    End Sub

    I left the Else: .body = .body & "No Log File" in the code above, because that is where I am at right now.  I have no idea what to try to get this to work, but I was thinking there should be a way to do it similiar to the myAttachments.Add where I can specify a value like olByValue to tell it where to insert the text.  Any help would be greatly appreciated. 

    Monday, September 24, 2012 8:10 PM

All replies

  • Untested but I should think that you should be able create another file with the required text and attach it if the log file does not exist.

    Regards, OssieMac

    Monday, September 24, 2012 10:41 PM
  • I would prefer to insert just text, so if anyone has suggestions, please let me know.  In the meantime, I will play around with your suggestion OssieMac, and hope additional suggestions for text come along.  Thanks!
    Tuesday, September 25, 2012 12:46 PM
  • I still need to place new text within my email body.  I really thought this should be as easy as it is to attach a file, but I'm just not figuring it out.  Help! Please!  And thank you in advance.

    OssieMac - Greatly appreciate your contribution.  I was able to get a file called NoLogFile to attach, but for whatever reason, they want text instead.

    Thanks, Trevor

    Wednesday, September 26, 2012 12:48 PM
  • I think that "luck" is the good word for that ;)

    If you want to add text from the log file to the body, you should to open using "open file for" method. [F1] and replace chr(10) to <br> in HTML text.

    If you add it to attachment like that, your recipient using no Exchange server, 'll have winmail.dat as attachment.


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Wednesday, September 26, 2012 7:02 PM
    Answerer
  • OssieMac - Greatly appreciate your contribution.  I was able to get a file called NoLogFile to attach, but for whatever reason, they want text instead.

    I am wondering if I am understanding correctly. Do you have a standard message for the body and you want to insert "No Log File" at a specific point in the message? If so, what about having the messge in multiple strings and then concatenate like the following. (Use of line feeds and how many line feeds is optional.) 

    .Body = string1 & vbCrlf & "No Log File"  & vbCrlf & string2

    How you concatenate and what you include in the concatenation can be controlled in an If / Else / End If


    Regards, OssieMac

    Thursday, September 27, 2012 12:04 AM
  • You are correct.  I just want to add the text "No Log File" at a specific point in the message.  I am calling the oft file that is already formatted, ie some bold words and some red words and such.  So when I call the email the body looks something like this:

    Insite Log

    <ex120926.log>  <--- This represents an attached file

    Internet Log

    No Log File  <--- (If there is no attachment I want it to put this instead)

    Mailsrv Log

    <ex120926.log>  <--- This represents an attached file

    and so on.

    I started off using String to Concatenate, but I couldn't get any rtf formatting.  That's when I discovered I could create an .oft file already formatted and then call it. 

    Does this make sense?

    Thanks again.


    Oh.  I was wondering if maybe i need to somehow set the cursor position and then insert text at cursor position.  I really don't know if something like that could work or how to do it, but maybe that's a possible direction?
    • Edited by Trevor09 Thursday, September 27, 2012 8:25 PM Thinking outloud
    Thursday, September 27, 2012 8:17 PM
  • VBATools - Thank you for your time in responding.  I don't actually want to pull text from the log file.  I am attaching the log file directly into the email as a specific point, but if the file doesn't exist, I want to place the text "No log file" in stead.

    Thanks again for your time.

    Thursday, September 27, 2012 8:20 PM
  • "I am attaching the log file directly into the email"

    - you should add it as attachment in HTML. RTF is bad idea for outside Exchange recipients.

    anyway

    "if the file doesn't exist, I want to place the text "No log file"

    You can use function for that. You've function FileThere(), but litlle modyf. like that:

    Function FileThere(FilePath As String) As Boolean
    On Error GoTo blad
        FileThere = Len(Dir(FilePath, vbDirectory Or vbHidden Or vbSystem)) > 0
    blad:
    End Function

    works like that:
    dim Myfile$: Myfile = "c:\file.txt"
    if FileThere(Myfile) = true then
    'yes you can add file as attachment .attachments.add Myfile
    else
    'no file - add string to youre .body = .body & "<br><br>" & "No attach file: " & Myfile
    end if

    regards


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Friday, September 28, 2012 10:06 AM
    Answerer
  • Thank you for your time Oskar Shon.  I like your function and the use of FileThere(Myfile) = true then statement.  It will simply some of my code, so thank you.  I agree HTML would be easier to do a lot of this, but the emails are internal only so I know the recipients will view in RTF and my boss wants the attachments in-line.

    As for the
    .body = .body & "<br><br>" & "No attach file: " & Myfile

    This seems to be creating issues. 
    1.  <br><br> is appearing simply as text.  I have removed the "" and tried () but to no avail. 
    2.  This is attaching to the end of the email rather than at the same point the attached file would have been placed
    3.  Stringing the body like this has removed all of the formatting from my message, for example no more bold words and no colored words.

    Here is my sample code with your logic added.

    Sub Test2()
        Dim myOlApp As Outlook.Application
        Dim myItem As Outlook.MailItem
        Dim myAttachments As Outlook.Attachments
        Dim OldName, NewName
        Dim objWord As Object
        Dim objSelection As Object
        Dim Myfile$
    
        Set myOlApp = CreateObject("Outlook.Application")
        Set myItem = myOlApp.CreateItemFromTemplate("C:\logs\Event Log.oft")
        Set myAttachments = myItem.Attachments
        
        With myItem
            Myfile = "c:\logs\insite\ex" & Format(Now() - 2, "yymmdd") & ".log"
            .subject = "Event Log " & Date
    
            If FileThere(Myfile) = True Then
            myAttachments.Add (Myfile), _
                olByValue, 605, "Insite Log"
            Else
            .body = .body & "<br><br>" & "No attach file: " & Myfile
            End If
            Myfile = "c:\logs\internet\ex" & Format(Now() - 2, "yymmdd") & ".log"
            If FileThere(Myfile) Then
                myAttachments.Add (Myfile) & ".log", _
                olByValue, 628, "Internet Log"
            Else:
            .body = .body & "<br><br>" & "No attach file: " & Myfile
            End If
        End With
        myItem.Display
    End Sub
    I did not attach it, but I have your function added above this code as well.  I can happily provide a sample of the "Event Log.oft" file upon request.  Don't think I can just upload it here, but I could email it upon request.

    Friday, September 28, 2012 2:37 PM
  • ok - change this

     "<br><br>"

    for

     vbNewLine

    I test code before I export oft, and change path to files I've got.

    and look on this:

    email footer'll add automatically if you've attach to account signatures.


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Friday, September 28, 2012 3:29 PM
    Answerer
  • Ok.  vbNewLine resolved the <b><b>, but here is a screen of what I need:

    Notice how all the formatting is preserved and the "No attach file: c:\logs\internet\ex120926.log" is under the proper heading for that log file. 

    If I use the .body = line above the "No attach file: c:\logs\internet\ex120926.log" is at the end of the email and I no longer have bold or reds, which looks like this:

    Thanks
    Friday, September 28, 2012 6:14 PM
  • Format text in RTF is complicate.

    This is a part of format i found:

    "{\rtf1\ansi\deff0{\fonttbl{\f0 Arial;}}{\colortbl ;\red0\green0\blue255;}\pard\cf1\f0\fs24 Test}"

    But to use this code you must using extra library

    Read this article: http://support.microsoft.com/?kbid=172038


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Friday, September 28, 2012 9:20 PM
    Answerer
  • Yes, this is getting more complicated.  I started to play around with this library and format, but quickly decided, this is above my head.  For now, I will rely on the attachment.add of a NoLogFile.txt file and keep plugging away at this rtf formatting stuff, although I don't think I will get far.  Thanks for your Help Oskar!

    Monday, October 1, 2012 3:27 PM
  • This is an extremely old post, but in case anyone comes across this in the future and is wondering how to do it, here is a great article about it: https://msdn.microsoft.com/en-us/library/dd492012(v=office.12).aspx
    Wednesday, May 11, 2016 1:38 PM
  • You are right. It is a very old thread, and the quoted article is also old and doesn't address what is arguably the best way to approach this with recent Outlook versions and that is to use the Outlook Word Editor, for then the editing of the message is much the same as editing a Word document using VBA. You can set ranges and write to those ranges.

    Graham Mayor - Word MVP
    www.gmayor.com

    Thursday, May 12, 2016 4:03 AM