我正在与excel一起处理用户输入,然后根据输入输出标准化的电子邮件,然后将格式化的文本保存到变量中,以便以后将其添加到剪贴板中,以便于输入到我们用于内部的系统中文档。
我有一种使用HTML作为电子邮件格式的有效方法,但这并没有解决我的意图,因为它也将HTML代码也复制到剪贴板或变量中。我希望获得Word的项目符号列表的功能,因此我一直在尝试以一种可以按需调用的方式修改MS Word代码。
我目前将默认的Excel库,用于Word和Outlook的表单库和对象库添加到了程序中。
我的目标是通过Word列表传递基于excel表构建的数组列表,并对其进行格式设置并将文本写入Outlook草稿中的Word编辑器中。需要写入的部分数量(不超过6个)会有所不同,通常每个部分不超过10个项目,通常更少。因此,我打算让其他子功能调用此格式以根据需要格式化每个部分。
随附的是此部分的输出示例,以及数据来自何处的示例。每个部分在Excel中都有自己的工作表。每个部分列表的第二级将来自单独的工作表。
我包括了一部分实际代码,这些代码显示了新Outlook草案的启动和文本输入。EmailBody()当前仅处理这些部分之外的任何文本,并为每个部分调用一个单独的函数来解析表(当前为未格式化的文本,仅输入换行符)。
输出范例
数据源示例
Sub Email()
Dim eTo As String
eTo = Range("H4").Value
Dim myItem As Object
Dim myInspector As Outlook.Inspector
Dim wdDoc As Word.Document
Dim wdRange As Word.Range
Set myItem = Outlook.Application.CreateItem(olMailItem)
With myItem
.To = eTo
.Bcc = "email"
.Subject = CNum("pt 1") & " | " & CNum("pt 2")
'displays message prior to send to ensure no errors in email. Autosend is possible, but not recommended.
.Display
Set myInspector = .GetInspector
'Obtain the Word.Document for the Inspector
Set wdDoc = myInspector.WordEditor
If Not (wdDoc Is Nothing) Then
Set wdRange = wdDoc.Range(0, wdDoc.Characters.Count)
wdRange.InsertAfter (EmailBody(CNum("pt 1"), CNum("pt 2")))
End If
'[...]
end with
end sub
我正在努力适应多级列表代码。我一直在代码的注释部分出现错误,并且不确定如何正确纠正它,以便它既起作用又可以按需调用:
运行时错误“ 450”:参数数量错误或属性分配无效
Sub testList()
Dim arr1 As Object
Set arr1 = CreateObject("System.Collections.ArrayList")
With arr1
.Add "test" & " $100"
.Add "apple"
.Add "four"
End With
Dim i As Long
With ListGalleries(wdBulletGallery).ListTemplates(1).ListLevels(1)
.NumberFormat = ChrW(61623)
.TrailingCharacter = wdTrailingTab
.NumberStyle = wdListNumberStyleBullet
.NumberPosition = InchesToPoints(0.25)
.Alignment = wdListLevelAlignLeft
.TextPosition = InchesToPoints(0.5)
.TabPosition = wdUndefined
.ResetOnHigher = 0
.StartAt = 1
.LinkedStyle = ""
End With
ListGalleries(wdBulletGallery).ListTemplates(1).Name = ""
'Selection.Range.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
' ListGalleries(wdBulletGallery).ListTemplates(1), ContinuePreviousList:= _
' False, ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:= _
' wdWord10ListBehavior
'writes each item in ArrayList to document
For i = 0 To arr1.Count - 1
Selection.TypeText Text:=arr1(i)
Selection.TypeParagraph
Next i
'writes each item to level 2 list
Selection.Range.SetListLevel Level:=2
For i = 0 To arr1.Count - 1
Selection.TypeText Text:=arr1(i)
Selection.TypeParagraph
Next i
Selection.Range.ListFormat.RemoveNumbers NumberType:=wdNumberParagraph
arr1.Clear
End Sub
Please forgive me if any of this seems inefficient, or an odd approach. I literally pickup up VBA a few weeks ago and only have a few hours of application in between my job responsibilities with what I've learned so far. Any assistance would be much appreciated.
The reason why you are getting that error is because, it is not able to resolve the object Selection
. You need to fully qualify the Selection object else Excel will refer to the current selection from Excel.
You may have referenced the Word Object library from Excel but that is not enough. The simplest way to reproduce this error is by running this from Excel
Sub Sample()
Selection.Range.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
ListGalleries(wdBulletGallery).ListTemplates(1), ContinuePreviousList:= _
False, ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:= _
wdWord10ListBehavior
End Sub
Here is a sample code which will work. To test this, open a word document and select some text and then run this code from Excel
Sub Sample()
Dim wrd As Object
Set wrd = GetObject(, "word.application")
wrd.Selection.Range.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
ListGalleries(wdBulletGallery).ListTemplates(1), ContinuePreviousList:= _
False, ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:= wdWord10ListBehavior
End Sub
将此应用于您的代码。您需要使用Word对象并完全限定您的对象,如Word Application,Word文档,Word Range等。例如
Dim oWordApp As Object, oWordDoc As Object
Dim FlName As String
FlName = "C:\MyFile.Docx"
'~~> Establish an Word application object
On Error Resume Next
Set oWordApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set oWordApp = CreateObject("Word.Application")
End If
Err.Clear
On Error GoTo 0
oWordApp.Visible = True
Set oWordDoc = oWordApp.Documents.Open(FlName)
With oWordDoc
'
'~~> Rest of the code here
'
End With
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句