读取包含文件路径的 Excel 文件并创建超链接 [英] Read Excel file containing file paths and create hyperlinks

问题描述

我正在尝试在 Outlook 中编写一个宏，该宏读取 Excel 文件，该文件在单独的单元格中具有完整路径和文件名，并将它们作为超链接插入到电子邮件中.

I'm trying to write a macro in Outlook that reads an Excel file that has full paths and filenames in separate cells and inserts them as hyperlinks in an email.

我找到了有关如何在 Outlook 中创建超链接的信息.我找不到任何关于 Outlook 如何从 Excel 工作表获取文件路径的信息.

I found information on how to create a hyperlink in Outlook. I can't find anything on how I Outlook would get the file paths from the Excel sheet.

错误说

编译错误:未定义用户定义的类型

Compile Error: User-defined type not defined

Sub links()
    Dim objExcel As New Excel.Application
    Dim exWb As Excel.Workbook
    Dim ExcelFileName As String
    Dim FilePath As String

    ExcelFileName = "C:\links.xlsx"
    Set exWb = objExcel.Workbooks.Open(ExcelFileName)

    FilePath = exWb.Sheets("Sheet1").Cells(1, 1)

    oMsg.TextBody = Chr(34) & FilePath & Chr(34)

End Sub

推荐答案

在 Outlook VBA 编辑器中设置对 Excel 的引用.

In the Outlook VBA editor set a reference to Excel.

Tools | References
Tick Microsoft Excel Object Library

向新模块添加 Option Explict.你会发现这很有帮助.

Add Option Explict to new modules. You will find this helpful.

Tools | Options | Editor tab
Tick Require Variable Declaration

.

Option Explicit

Sub links()

Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook
Dim ExcelFileName As String
Dim FilePath As String

Dim oMsg As mailItem

ExcelFileName = "C:\links.xlsx"

Set exWb = objExcel.Workbooks.Open(ExcelFileName)

FilePath = exWb.Sheets("Sheet1").Cells(1, 1)

On Error Resume Next
Set oMsg = ActiveInspector.currentItem
On Error GoTo 0
If oMsg Is Nothing Then
    Set oMsg = CreateItem(0)
    oMsg.Display
End If

' This adds to existing text. 
' Must display first to save a signature
'oMsg.body = Chr(34) & FilePath & Chr(34) & oMsg.body
'or
oMsg.HTMLBody = Chr(34) & FilePath & Chr(34) & oMsg.HTMLBody

ExitRoutine:
    Set oMsg = Nothing
    Set exWb = Nothing
    Set objExcel = Nothing

End Sub

这篇关于读取包含文件路径的 Excel 文件并创建超链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！