如何传递vbscript变量到html

Question

我正在使用vbscript和excel自动执行我的任务之一。下面提到的代码在outlook中自动起草一封带有附件的电子邮件。但我仍然必须手动输入'aa'值和代码'1'值。这些值是在excel中定义的，我想从那里获取它。我可以从objFso.GetFileName(objFile.path)获得"名称"值。但是我不确定如何在HTML代码中传递这些值。

 For Each objFolder In objFolder.SubFolders
    For Each objFile In objFolder.Files
        If objFso.GetExtensionName(objFile.Path) = "xls" Then

Set MyApp = CreateObject("Outlook.Application")
Set MyItem = MyApp.CreateItem(0) 'olMailItem
With MyItem
    .To = "[e-mail]"
    .Subject = ""
    .ReadReceiptRequested = False
    .HTMLBody = "<font size='3' face='Calibri' color='#203B78'>Hi,<BR><BR>Please approve the attached doc for **<b>aa</b>** for code **<b>1</b>.** 

    .Attachments.Add objFolder.Path & "" & objFso.GetFileName(objFile.path)
End With
MyItem.Display
End if
  Next
Next    

请建议!!

Answer 1

你需要打开Excel文件

Set xl = CreateObject("Excel.Application")
xl.Visible = True  'set to False for production use
Set wb = xl.Workbooks.Open("C:pathtoyour.xls")
Set ws = wb.Sheets(1)

并将值插入到邮件正文中:

With MyItem
  .HTMLBody = "... approve the attached doc for **<b>" _
    & ws.Cells(1, 1).Value & "</b>** for code **<b>" _
    & ws.Cells(1, 2).Value & "</b>.**"

将1, 1和1, 2替换为实际值所在单元格的行号和列号

完成后关闭工作簿并退出Excel:

wb.Close
xl.Quit

---

另一个注意事项是，objFile.Path已经拥有文件的完整路径，因此您可以简单地使用

.Attachments.Add objFile.Path

不是

.Attachments.Add objFolder.Path & "" & objFso.GetFileName(objFile.path)

如果出于某种原因您必须从文件夹和文件名构建路径，使用BuildPath方法应该优先于通过字符串连接手动构建路径:

.Attachments.Add objFso.BuildPath(objFolder.Path, objFile.Name)

编辑:文件名可以这样转换:

name = Replace(Mid(objFso.GetBaseName(objFile.Name), 26), "_", " ")

长版:

name = objFso.GetBaseName(objFile.Name) 'get file name without extension
name = Mid(name, 26)             'get part after "Timecard Adjustment_Form_"
name = Replace(name, "_", " ")   'replace underscore with space