使用VBA调用VLookup时出现类型不匹配错误的求助

我正在为团队开发一款自动生成邮件内容的表格应用，由于团队成员使用不同的邮件客户端，目标是将数据写入Microsoft剪贴板以便粘贴。为保留格式，我创建了一个包含待拼接文本片段的表格，共有四个邮件模板，其中一个无法正常工作。

相关代码片段 #

If EmailTemplate = "How-To" Then

    EmailSub = "OAS: How-To Guide"
    
    EmailCode1 = Evaluate("VLookup(Email_Type,EmailBodies,2,False)")
    
    RepEmail = Evaluate("VLookup(Email_Type, EmailBodies, 3, False)")
    
    EmailCode2 = Evaluate("VLookup(Email_Type, EmailBodies, 4, False)")
    
    RepCalendar = Evaluate("VLookup(Email_Type, EmailBodies, 5, False)")
    
    EmailCode3 = Evaluate("VLookup(Email_Type, EmailBodies, 6, False)")
    
ElseIf EmailTemplate = "PQW" Then

    EmailSub = "When You Are Ready, Please Tell Us About Your Project Requirements"
    
    EmailCode1 = Evaluate("VLookup(Email_Type,EmailBodies,2,False)")
    
    RepEmail = Evaluate("VLookup(Email_Type, EmailBodies, 3, False)")
    
    EmailCode2 = Evaluate("VLookup(Email_Type, EmailBodies, 4, False)")
    
    RepCalendar = Evaluate("VLookup(Email_Type, EmailBodies, 5, False)")
    
    EmailCode3 = Evaluate("VLookup(Email_Type, EmailBodies, 6, False)")
End If

错误现象 #

通过VLookup函数引用表格数据时，当EmailTemplate = "How-To"时一切正常，但当为"PQW"时，给变量EmailCode1赋值时出现类型不匹配错误。

对应文本内容 #

"How-To"对应的单元格文本：

Thank you for your interest in Open Automation Software. Please see the links below for guidance on accomplishing your specified requirements:

"PQW"对应的单元格文本：

Please tell us more about your project requirements. By filling out this worksheet, it will make it easier for us to help you focus your efforts on evaluating the parts of our software that are important to you.

Please take a few minutes to fill out and email back to

两段文本都包含换行符且仅为文本，代码中所有变量均为String类型。

已尝试的解决方法 #

• 将单元格数据类型设置为文本
• 验证VLookup搜索的字符串与表格中的内容完全匹配
• 将所有变量的数据类型改为Variant
• 删除表格中相关行并手动重建
• 在单元格中输入相同公式，能正常返回数据

其他模板使用相同公式和数据类型均能正常工作，附截图显示VLookup公式在单元格中正常返回数据。

内容的提问来源于stack exchange，提问作者Zach Walsh