Hi All-
I'm trying to use VBA to find and replace a bunch of info in an email. Essentially, I'm hitting a button in an access form that:
1) Opens up a Word template
2) Replaces text within the word template using information on the form
I'm running into issues when trying to replace something with a memo field. I get the error "Run-time error '5854': String Parameter too long.
Here's my code... any ideas on how to either work around the 255 text character limit or chunk up my memo field?
Code:
Private Sub btnEmailStuff_Click()
'opens word file
Dim db As DAO.Database
Dim objWord As Word.Application
Set objWord = New Word.Application
With objWord
.Documents.Open "path to_Summary_Template.docx"
.Documents("path to_Summary_Template.docx").Activate
.Visible = True
End With
'Replaces first thing
With objWord
.Activate
End With
'this part works fine - I've actually got five of these that all work fine before the one that fails.
With objWord.Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "Replace Number 1"
.Replacement.Text = Me.Field1
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute Replace:=2 'wdReplaceAll
End With
'this part fails
'Replaces memo-length info
With objWord
.Activate
End With
With objWord.Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "Replace big info"
.Replacement.Text = Me.BigInfoMemoField
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute Replace:=2 'wdReplaceAll
End With
'copies word document
With objWord
.Activate
.Selection.WholeStory
.Selection.Copy
End With
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
.SentOnBehalfOfName = "my email"
.To = Me.Email
'.CC = ""
'.bcc = ""
.Subject = "Info people need - " & Format(Date, "YYYYMMDD")
.HTMLBody = ActiveDocument.Range.FormattedText
.Display
End With
End Sub
Many thanks!