R – A couple of questions about Word macros

excelms-wordvba

I need to grab a list of names from Excel and insert them into a Word document, printing one document per name. The document has some text and a bookmark called "name". The code is below.

First, I want to know if it's possible to detect how long is the list of names in the Excel spreadsheet and grab that, instead of hardcoding the number.

Second, I can't figure out how to delete the text I already put inside the document. When I insert text in a bookmark, it gets appended after the bookmark, so if I keep adding names they all stack together.

Maybe with the code this will be clearer:

Sub insertar_nombre()
    Dim Excel As Excel.Application
    Dim Planilla As Excel.Workbook
    Dim Hoja As Excel.Worksheet

    Set Excel = CreateObject("Excel.Application")
    Dim Filename As String
    Dim fname As Variant
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        .Title = "Seleccionar Documento de Excel"
        .Show
        For Each fname In .SelectedItems
            Filename = fname
        Next
    End With
    Set Planilla = Excel.Workbooks.Open(Filename)
    Set Hoja = Planilla.Worksheets(1)
    Dim Nombre As String
    For Count = 2 To 10
        Nombre = Hoja.Cells(Count, 1).Value
        ActiveDocument.Bookmarks("name").Range.Text = Nombre
        ActiveDocument.PrintOut
    Next
End Sub

Forgive me if this code is obviously wrong or something, I'm just beginning with this.

Best Answer

I need to grab a list of names from Excel and insert them into a Word document, printing one document per name.

Why don't you simply use the mail merge feature?

Related Solutions

How to display code snippets in MS Word preserving format and syntax highlighting

Here is the best way, for me, to add code inside word:

Go to Insert tab, Text section, click Object button (it's on the right)
Choose OpenDocument Text which will open a new embedded word document
Copy and paste your code from Visual Studio / Eclipse inside this embedded word page
Save and close

Advantages

The result looks very nice. Here are the advantages of this method:

The code keeps its original layout and colors
The code is separated from the rest of the document, as if it was a picture or a chart
Spelling errors won't be highlighted in the code (this is cool !)

And it takes only few seconds.

Excel – How to add excel range as a picture to outlook message body

This is a good question, Asaf. When I have built automated e-mail solutions, I've found it difficult to get the signature line in. It's possible, but not easy. Maybe it's updated in 2010, but I haven't checked yet.

What I do is place the entire body into a text file on a drive, complete with any html tags that I want for formatting. This gives me great flexibility in both making nicely formatted e-mails where I can assign variables as well.

I then access those files through the Microsoft Scripting Runtime library.

See below code snippets:

Option Explicit

Const strEmailBoiler As String = "\\server\path\folder\subfolder\email_text\"

Sub PrepMessage()

Dim strBody As String, strMon As String

strMon = range("Mon").Value
strFY = range("FY").Value
strBody = FileToString(strEmailBoiler, "reports_email_body.txt")

strBody = Replace(strBody, "[MONTH]", strMon)
strBody = Replace(strBody, "[YEAR]", Right(strFY, 2))
strBody = Replace(strBody, "[FILE PATH]", strFileName)

SendMail "firstname.lastname@xyz.com", "Subject Goes Here " & strMon & " YTD", strBody

End Sub

Function FileToString(ByVal strPath As String, ByVal strFile As String) As String
'requires reference to Microsoft Scripting Runtime Object Library (or late binding)

    Dim ts As TextStream

    Set fso = New FileSystemObject
    Set ts = fso.OpenTextFile(strPath & strFile, ForReading, False, TristateUseDefault)

    FileToString = ts.ReadAll
    ts.Close

    Set ts = Nothing
    Set fso = Nothing

End Function

Sub SendMail(strTo As String, strSubject As String, strHTMLBody As String, Optional strAttach As String, Optional strCC As String)
'requires reference to Microsoft Outlook X.X Object Library (or late binding)

Dim olApp As Outlook.Application
Dim olMI As Outlook.MailItem

Set olApp = CreateObject("Outlook.Application")
Set olMI = olApp.CreateItem(olMailItem)

With olMI

    .To = strTo
    .Subject = strSubject
    .HTMLBody = strHTMLBody
    If strAttach <> vbNullString Then .Attachments.Add strAttach
    .Display 'using this because of security access to Outlook
    '.Send

End With

End Sub

Then my reports_email_body.txt file will look like this:

<p>Hello Person,</p> 
<p>The Reports file for [MONTH] FY[YEAR] has been saved in the following location:</p>
<p><a href="[FILE PATH]">[FILE PATH]</a></p>
<p>Best,</p>
<br>
Scott Holtzman
<br>My Address
<br>my title
<br>whatever else...

Related Topic