从excel电子表格中制​​作主题分配电子邮件

Question

我的VBA知识极其有限。 我有一个领导联系人的Excel电子表格。我想设置一个下拉列表，向我选择的特定人员发送电子邮件，然后返回电子邮件正文中的联系人信息。我不知道如何让电子邮件自动填充，现在，弹出的电子邮件在联系人信息的正文中具有“真实”，而不是返回单元格中的文本值。如何以及在哪里我添加代码来解决这个问题？

Sub DropDown7_Change() 

    Dim answer As String 

    answer = MsgBox("Are you sure you want to assign this lead?", _ 
     vbYesNo, "Send Email") 
    ' Above code informs the user that an automated email will be sent 

    'Code uses the users answer to either carryout the generated email process or to not save the changes. 
    If answer = vbNo Then Cancel = True 
    If Cancel = True Then Exit Sub 

    If answer = vbYes Then 
     'Connects to outlook and retrieves information needed to create and send the email. 
     Set OutlookApp = CreateObject("Outlook.Application") 
     Set OlObjects = OutlookApp.GetNamespace("MAPI") 
     Set newmsg = OutlookApp.CreateItem(olMailItem) 

     'Contains the email address of the person receiving the email. 
     newmsg.Subject = "Lead Assigned to You" 'Sets the automated subject line to the email 
     newmsg.Body = "Hello," & vbNewLine & _ 
      "You have been assigned a lead. Please follow up with the contact" & vbNewLine & _ 
      ActiveCell.Offset(0, 3).Range("K5").Select 
      ActiveCell.Offset(0, 6).Range("K5").Select 
      ActiveCell.Offset(0, 7).Range("K5").Select 

     'Above code has the body of the automated email 
     newmsg.Display 
    End If 

End Sub ' End of function 

Answer 1

如果你试图让那些Offset到Range("K5")值，那么你需要使用Offset与.Value，这样Range("K5").Offset(0, 3).Value，这将获得价值3列单元格“K5”的权利。

下面的代码，将从3个细胞列偏移细胞“K5”给你添加值邮件正文：

Sub DropDown7_Change() 

    Dim answer As String 

    answer = MsgBox("Are you sure you want to assign this lead?", _ 
     vbYesNo, "Send Email") 
    ' Above code informs the user that an automated email will be sent 

    'Code uses the users answer to either carryout the generated email process or to not save the changes. 
    If answer = vbNo Then 
     Exit Sub 
    Else 
     If answer = vbYes Then 

      'Connects to outlook and retrieves information needed to create and send the email. 
      Set OutlookApp = CreateObject("Outlook.Application") 
      Set OlObjects = OutlookApp.GetNamespace("MAPI") 
      Set newmsg = OutlookApp.CreateItem(olMailItem) 

      'Contains the email address of the person receiving the email. 
      newmsg.Subject = "Lead Assigned to You" 'Sets the automated subject line to the email 
      newmsg.body = "Hello," & vbNewLine & _ 
       "You have been assigned a lead. Please follow up with the contact" & vbNewLine & _ 
       Range("K5").Offset(0, 3).Value & vbNewLine & _ 
       Range("K5").Offset(0, 6).Value & vbNewLine & _ 
       Range("K5").Offset(0, 7).Value & vbNewLine      

      'Above code has the body of the automated email 
      newmsg.Display 
     End If 
    End If 

End Sub