Excel中的自动化电子邮件 [英] Automation email in Excel

问题描述

我正在尝试设计一个能够帮助我的公司进行招聘的Excel文件.

I am trying to design an excel file that will help my company with recruitment.

任务是为公司与之交谈的候选人创建一张工作表，我们将记录该候选人的所有记录，包括其姓名，姓氏，手机和电子邮件地址.您可以在此处查看所有内容的屏幕截图: https://imgur.com/gallery/tvAIx

The task is to create a sheet for candidates that the company speaks to, we will record all the records for the candidates including their first name, last name, mobile and email address. You can see a screenshot of how everything looks here: https://imgur.com/gallery/tvAIx

如您所见，公司何时与应聘者交谈以及应聘者将简历发送给我们时，都有一些列.最后，还有一个简历提醒"列.它具有以下代码=IF(ISBLANK(F2), HYPERLINK("mailto:" & D2 & "?subject=" & $O$3 & "&body=" & $P$3, "Send reminder"), "All good")

As you can see there are columns for when the company speaks to the candidate and when he sends his CV to us. At the end there is also a "CV reminders" column. It has the following code =IF(ISBLANK(F2), HYPERLINK("mailto:" & D2 & "?subject=" & $O$3 & "&body=" & $P$3, "Send reminder"), "All good")

这样的想法是，如果尚未收到简历，则可以按该单元格，它将为该候选人生成提醒电子邮件.我想使所有流程都具有自主性，以便它可以从相关单元格中选择候选人姓名，并向他发送通用电子邮件，例如:

The idea is so that if CV has not been received yet, you can press the cell and it will generate a reminder email for the candidate. I want to make all the process autonomous so that it can pick out the candidate name from the relevant cell and send him a generic email like:

来自单元格的姓名，

希望你很好.

我们已经在单元格中的日期与您进行了交谈.您有没有机会审查自己的简历?你有什么问题吗?"

We have spoken with you on date from cell. Have you had a chance to review your CV yet? Do you have any questions?"

我确信VBA可能不知道怎么做.谢谢.

I am sure it is possible with VBA just don't know how. Thank you.

推荐答案

您应该能够处理VBA的基本用法.

You should be able to handle basic VBA usage in order to achieve this.

下面是发送Office 2000-2016的Outlook电子邮件的VBA代码.来源是 http://www.rondebruin.nl

Below is the VBA code that sends an Outlook e-mail message for Office 2000-2016. Source is http://www.rondebruin.nl

您可以将代码放入请求的单元格的SelectionChange事件中，并根据需要更改Body，SendTo等部分. (显然，在您的情况下，SendTo地址和正文"的某些部分将来自所选单元格行中的特定单元格.)

You may put the code in the SelectionChange event of the requested cell(s) and change the Body, SendTo etc. portions according to your needs. (Appearently in your case, SendTo address and some parts of Body will come from particular cells on the row of your selected cell)

Sub Mail_small_Text_Outlook()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2016
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    On Error Resume Next
    With OutMail
        .To = "[e-mail]"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

这篇关于Excel中的自动化电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！