英文:
VBA paste defined message follow by excel paste range into outlook email
问题
Sure, here are the translated code sections you requested:
-
Original:
wordDoc.Range.PasteAndFormat wdChartPicture wordDoc.Range.InsertAfter vbCrLf & vbCrLf & emailMessage
Translated:
wordDoc.Range.PasteAndFormat wdChartPicture wordDoc.Range.InsertAfter vbCrLf & vbCrLf & emailMessage
-
Original:
wordDoc.Range.InsertAfter vbCrLf & vbCrLf & emailMessage
Translated:
wordDoc.Range.InsertAfter vbCrLf & vbCrLf & emailMessage
-
Original:
wordDoc.Range.PasteAndFormat wdChartPicture
Translated:
wordDoc.Range.PasteAndFormat wdChartPicture
英文:
The VBA code is suppose to
-
Paste the defined text into email body [emailMessage]
-
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:
-
wordDoc.Range.PasteAndFormat wdChartPicture wordDoc.Range.InsertAfter vbCrLf & vbCrLf & emailMessage
into:
results: no emailmessage. paste range values is not showing too.
-
wordDoc.Range.InsertAfter vbCrLf & vbCrLf & emailMessage
-
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
类的 Start
和 End
属性,以确定要粘贴文本和数据/图像的位置。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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论