VBA粘贴定义的消息,然后将Excel粘贴范围到Outlook电子邮件中。

huangapple go评论76阅读模式
英文:

VBA paste defined message follow by excel paste range into outlook email

问题

Sure, here are the translated code sections you requested:

  1. Original:

    wordDoc.Range.PasteAndFormat wdChartPicture
    wordDoc.Range.InsertAfter vbCrLf & vbCrLf & emailMessage
    

    Translated:

    wordDoc.Range.PasteAndFormat wdChartPicture
    wordDoc.Range.InsertAfter vbCrLf & vbCrLf & emailMessage
    
  2. Original:

    wordDoc.Range.InsertAfter vbCrLf & vbCrLf & emailMessage            
    

    Translated:

    wordDoc.Range.InsertAfter vbCrLf & vbCrLf & emailMessage
    
  3. Original:

    wordDoc.Range.PasteAndFormat wdChartPicture
    
    

    Translated:

    wordDoc.Range.PasteAndFormat wdChartPicture
    
    
英文:

The VBA code is suppose to

  1. Paste the defined text into email body [emailMessage]

  2. Next, paste the excel range [filteredRange] below the defined text into the email body.

    However, the VBA code paste 2) follow by 1) in this sequence. I'm unable to correct this.

    Code:

        ' Loop through each filter criteria and apply the filter
        For Each criteria In filterValues
            filterRange.AutoFilter Field:=1, Criteria1:=criteria, Operator:=xlFilterValues
    
            ' Copy the visible range to a new worksheet and resize it to fit the content
            Dim filteredRange As Range
            Set filteredRange = filterRange.SpecialCells(xlCellTypeVisible)
    
            ' Check if any visible cells were found
            If Not filteredRange Is Nothing Then
                ' Create a new email
                Dim outlookApp As Object
                Set outlookApp = CreateObject("Outlook.Application")
    
                Dim outlookMail As Object
                Set outlookMail = outlookApp.CreateItem(0)
    
                ' Define the email message
                Dim emailMessage As String
                emailMessage = "Hi, please find below the summary for " & criteria & " Cash Pool " & ws.Range("C1") & ":" & vbCrLf & vbCrLf
    
                ' Define the folder path where attachments are
                Dim folderPath As String
                folderPath = "C:\Users\qte3004\Desktop\SG\"
    
                ' Check if the folder exists
                If Dir(folderPath, vbDirectory) <> "" Then
                    ' Loop through all the files in the folder
                    Dim fileName As String
                    fileName = Dir(folderPath & criteria & "*.*")
                    Do While fileName <> ""
                        ' Attach each file with name starting with the criteria to the email
                        outlookMail.Attachments.Add folderPath & fileName
                        fileName = Dir()
                    Loop
                End If
    
    
                ' Copy the filtered range and paste it into the email body
                filteredRange.Copy
                Dim wordDoc As Object
                Set wordDoc = outlookMail.GetInspector.WordEditor
                wordDoc.Range.PasteAndFormat wdChartPicture
                wordDoc.Range.InsertAfter vbCrLf & vbCrLf & emailMessage
    
                ' Check if the criteria is present in the dictionary
                If recipientsDict.Exists(criteria) Then
                    ' Retrieve the recipient list associated with the criteria
                    Dim recipients As String
                    recipients = recipientsDict(criteria)
                    ' Add the recipient list to the email
                    outlookMail.To = recipients
                    outlookMail.CC = criteriaSheet.Range("H2")
                End If
    
                outlookMail.Subject = criteria & " Cash Pool " & ws.Range("C1")
                outlookMail.Display
    
            End If
    

Tried to swap the code from:

  1.             wordDoc.Range.PasteAndFormat wdChartPicture
                wordDoc.Range.InsertAfter vbCrLf & vbCrLf & emailMessage
    

    into:

    results: no emailmessage. paste range values is not showing too.

  2. wordDoc.Range.InsertAfter vbCrLf & vbCrLf & emailMessage            
    
  3. wordDoc.Range.PasteAndFormat wdChartPicture
    
    

答案1

得分: 1

将电子邮件消息的值放入.body中

    outlookMail.Body = "嗨,请查看以下摘要:" & criteria & "现金池" & ws.Range("C1") & ":" & vbCrLf & vbCrLf

    Dim wordDoc As Object, x
    Set wordDoc = outlookMail.GetInspector.WordEditor
    x = wordDoc.Range.End - 1
    filteredRange.Copy
    wordDoc.Range(x).Paste
英文:

put the emailmessage value inside the .body

    outlookMail.Body = "Hi, please find below the summary for " & criteria & " Cash Pool " & ws.Range("C1") & ":" & vbCrLf & vbCrLf

    Dim wordDoc As Object, x
    Set wordDoc = outlookMail.GetInspector.WordEditor
    x = wordDoc.Range.End - 1
    filteredRange.Copy
    wordDoc.Range(x).Paste

答案2

得分: 0

尝试指定 Range 类的 StartEnd 属性,以确定要粘贴文本和数据/图像的位置。Document.Range 方法使用指定的起始和结束字符位置返回一个 Range 对象。

注意,你可以在 Word 中使用宏录制器获取为你生成的代码,这样你可以在自动化代码中重用属性和方法。有关更多信息,请参阅 创建或运行宏

英文:

Try to specify the Start and End properties of the Range class where to paste text and data/images. The Document.Range method returns a Range object by using the specified starting and ending character positions.

Note, you can use a macro recorder available in Word to get the code generated for you, so you could re-use properties and methods in your automation code. See Create or run a macro for more information.

huangapple
  • 本文由 发表于 2023年4月13日 16:17:11
  • 转载请务必保留本文链接:https://go.coder-hub.com/76003182.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定