Results 1 to 6 of 6
  1. #1
    Goodge12 is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2008
    Posts
    25

    VBA - copy memo, paste as text in email app

    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!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Can break up the memo field data into 255 character segments but don't know how that will help you get it into Word document.

    Why do you need Word?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Goodge12 is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2008
    Posts
    25
    Thanks for the reply, June7. I am using Word because there is a template with some images and formatting that I need to maintain - I'm putting everything into Word, then copying the contents and pasting into Outlook.

    Is there a way to maybe determine how many characters there are, break it up into pieces, then do a find/replace with a variable number of chunks?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Images can be embedded into the email body using HTML tags. HTML tags can also format text.

    As stated, the memo text can be broken up. I suppose the Replace code could be within a loop that is iterated by the number of pieces.

    Personally, I would eliminate the Word middleman.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Goodge12 is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2008
    Posts
    25
    Sounds good, I'll give it a try. Thanks!

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    All of the body literal text and HTML tags must be enclosed within quote marks and variables are concatenated, like:

    .HTMLBody = "<HTML><H2>This is email message.</H2></BR><BODY>" & Me.textbox & "</BODY></HTML>"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Copy text file to Memo Field
    By HDobler in forum Programming
    Replies: 5
    Last Post: 06-25-2021, 02:21 AM
  2. Copy and Paste Row (vb)
    By Computer202 in forum Programming
    Replies: 7
    Last Post: 03-28-2014, 01:59 AM
  3. Any way to copy paste records from
    By super12 in forum Access
    Replies: 5
    Last Post: 03-05-2013, 11:16 PM
  4. Copy-Paste
    By BorisGomel in forum Access
    Replies: 4
    Last Post: 10-25-2011, 07:17 AM
  5. Copy/paste to new record.
    By xbox1513 in forum Forms
    Replies: 1
    Last Post: 02-23-2011, 04:52 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums