Note: The other languages of the website are Google-translated. Back to English
ログイン  \/ 
x
or
x
今すぐ登録  \/ 
x

or

Excelのセル値に基づいて電子メールを自動的に送信するにはどうすればよいですか?

Excelで指定されたセル値に基づいて、Outlookを介して特定の受信者に電子メールを送信するとします。 たとえば、ワークシートのセルD7の値が200より大きい場合、電子メールが自動的に作成されます。 この記事では、この問題をすばやく解決するためのVBAメソッドを紹介します。

VBAコードを使用してセル値に基づいて電子メールを自動的に送信する


VBAコードを使用してセル値に基づいて電子メールを自動的に送信する

Excelのセル値に基づいてメールを送信するには、次のようにしてください。

1.ワークシートで、セルの値(ここではセルD7と言います)に基づいて電子メールを送信する必要があります。シートタブを右クリックして、[ コードを表示 コンテキストメニューから。 スクリーンショットを参照してください:

2.ポップアップで Microsoft Visual Basic for Applications ウィンドウの場合は、以下のVBAコードをコピーしてシートコードウィンドウに貼り付けてください。

VBAコード:Excelのセル値に基づいてOutlook経由でメールを送信する

Dim xRg As Range
'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
  Set xRg = Intersect(Range("D7"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value > 200 Then
        Call Mail_small_Text_Outlook
    End If
End Sub
Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2"
    On Error Resume Next
    With xOutMail
        .To = "Email Address"
        .CC = ""
        .BCC = ""
        .Subject = "send by cell value test"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

ノート:

1. VBAコードでは、 D7 値> 200 は、メールを送信するセルとセルの値です。

2.必要に応じてメール本文を変更してください xMailBody コードの行。

3.電子メールアドレスを受信者の電子メールアドレスに置き換えます .To = "メールアドレス".

4.必要に応じて、CcおよびBccの受信者を指定します。 .CC =“” Bcc =“” セクション。

5.最後にメールの件名を行で変更します .Subject = "セル値テストで送信".

3。 プレス 他の + Q キーを一緒に閉じて Microsoft Visual Basic for Applications 窓。

今後、セルD7に入力する値が200を超えると、指定した受信者と本文を含む電子メールがOutlookに自動的に作成されます。 あなたはクリックすることができます 送信 このメールを送信するボタン。 スクリーンショットを参照してください:

ノート:

1. VBAコードは、電子メールプログラムとしてOutlookを使用している場合にのみ機能します。

2.セルD7に入力されたデータがテキスト値の場合、電子メールウィンドウもポップアウトされます。


Excelで作成されたメーリングリストのフィールドに基づいて、Outlookを介して簡単に電子メールを送信します。

  メールを送る の有用性 Kutools for Excel Excelで作成されたメーリングリストに基づいてOutlookを介して電子メールを送信するユーザーを支援します。
今すぐダウンロードしてお試しください! (30日間の無料トレイル)


関連記事:


最高のオフィス生産性ツール

Kutools for Excelはほとんどの問題を解決し、生産性を80%向上させます

  • 再利用: すばやく挿入 複雑な数式、チャート および以前に使用したものすべて。 セルを暗号化する パスワード付き。 メーリングリストを作成する そしてメールを送る...
  • スーパーフォーミュラバー (複数行のテキストと数式を簡単に編集できます); 読書レイアウト (多数のセルを簡単に読み取って編集する); フィルター範囲に貼り付け...
  • セル/行/列をマージする データを失うことなく; 分割セルコンテンツ; 重複する行/列を組み合わせる...重複セルを防止します。 範囲を比較する...
  • [複製]または[一意]を選択します 行; 空白行を選択 (すべてのセルは空です); スーパーファインドとファジーファインド 多くのワークブックで; ランダム選択...
  • 正確なコピー 数式参照を変更せずに複数のセル。 参照の自動作成 複数のシートに; 箇条書きを挿入、チェックボックスなど...
  • テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷。 セルの内容とコメントを変換する...
  • スーパーフィルター (フィルタースキームを保存して他のシートに適用します); 高度な並べ替え 月/週/日、頻度など。 特殊フィルター 太字、斜体...
  • ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。 データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
  • 300以上の強力な機能。 Office / Excel2007-2019および365をサポートします。すべての言語をサポートします。 企業や組織に簡単に導入できます。 全機能30日間の無料トライアル。 60日間の返金保証。
kteタブ201905

Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性が50%向上し、毎日何百ものマウスクリックが減ります。
officetab下部
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Sophie · 2 years ago
    Hi, I have used this code and I need it to send an email when its 0 but its sending emails even whe its above 0. Can you help?
  • To post as a guest, your comment is unpublished.
    griffd12 · 2 years ago
    My excel workbook consist of 30+ worksheets. Each sheet is a duplicate that has same structure. I have columns that are set as validation (drop down list) and a field that is a VLOOKUP formula.

    Validation = C:5
    VLOOKUP formula is = D:5

    When I change the lookup value in C:5 it changes the value in D:5 (Note that C:5 is text not a number) Because D:5 is configured as a VLOOKUP the VB script dos not work even though cell D:5 shows the value of 2. If I delete the formula in D:5 and simple enter a number 2 then it works. How can I get this to work with the VLOOKUP cell or even by just changing the validation cell. Example if C:5 = "New" then send email. Or if C:5 changes from New to "Escalate" send an email and last if C:5 changes to "Closed" send a email??

    Also one last item is when the email is created how can the email in the subject line or in the body contain the name of the worksheet that was changed? right now i just get a email but i don't know which worksheet out of the workbook was changed.


    Dim xRg As Range
    'Update by Extendoffice 2018/3/7
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    Set xRg = Intersect(Range("D5"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value = 2 Then
    Call Mail_small_Text_Outlook
    End If
    End Sub
    Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
    "This is line 1" & vbNewLine & _
    "This is line 2"
    On Error Resume Next
    With xOutMail
    .To = "Email Address"
    .CC = ""
    .BCC = ""
    .Subject = "send by cell value test"
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub
  • To post as a guest, your comment is unpublished.
    Derek · 2 years ago
    Hello. Using your VB Script works when the value I enter is done manually. My cell is a VLOOKUP formula. The VB script to send email based on cell D5 but D5 is a formula not a manual entry number. How can I get this to work with a VLOOKUP cell?

    Dim xRg As Range
    'Update by Extendoffice 2018/3/7
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    Set xRg = Intersect(Range("D5"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value = 1 Then
    Call Mail_small_Text_Outlook
    End If
    End Sub
    Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
    "This is line 1" & vbNewLine & _
    "This is line 2"
    On Error Resume Next
    With xOutMail
    .To = "Email Address"
    .CC = ""
    .BCC = ""
    .Subject = "send by cell value test"
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub
  • To post as a guest, your comment is unpublished.
    Derek · 2 years ago
    Hello. First let me thank you for your information on this topic. it's been very helpful. I have several sheets in a single excel workbook. On Each sheet there will be 3 VB click boxes that will send an email. What I am wondering is, how can I include the sheet name in the email/VB code? Below is my code that i am using for the 3 click boxes (note I just change some of email body and subject.

    Private Sub CommandButton1_Click()
    'Updated by Extendoffice 2017/9/14
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    On Error Resume Next
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hello" & vbNewLine & vbNewLine & _
    "Please note that a new software case has been added." & vbNewLine & _
    "Please review and follow-up with customer service rep for status updates"
    On Error Resume Next
    With xOutMail
    .To = "email address"
    .CC = ""
    .BCC = ""
    .Subject = "A New Software Case Has Been Added"
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub
  • To post as a guest, your comment is unpublished.
    Janelle · 2 years ago
    Hi, I am trying to write a VBA for email from excel. Basically if an individual's name is selected in the corresponding cell excel will trigger an email to that person. I have created a reference list of names and emails that are being used for data validation.

    I also want to return particular values in the email body that are relevant to the row number the name is entered to.

    e.g R9C21 (U9) = Joe Bloggs. Email joe.bloggs@bloggs.com >>>>>>>>>>> This value must be sourced from the data validation list (one of multiple name and email address combinations.

    As a result. Excel opens an email and the email body will be populated with the following cells

    R9C12 & "Random Text" & R9C10

    The email process should be triggered based on any name value entered in C[21] and the email body must contain cell values from the corresponding row.

    This is what I have. This does not allow for multiple name options, rows or allow me to enter cell references in the email body:

    Dim xRg As Range
    'Update by Extendoffice 2018/3/7
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    Set xRg = Intersect(Range("R9C21"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value = "Name" Then
    Call Mail_small_Text_Outlook
    End If
    End Sub
    Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
    "This is line 1" & vbNewLine & _
    "Test"
    On Error Resume Next
    With xOutMail
    .To = "Email"
    .CC = "Email"
    .BCC = ""
    .Subject = "send by cell value test"
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @MrBain Good day,
    Sorry can't help you with that.
    Welcome to post any question in our forum: https://www.extendoffice.com/forum.html to get more Excel support from out Excel professional or other Excel fans.
  • To post as a guest, your comment is unpublished.
    asint · 2 years ago
    how if there are several cells, each cell has a different value
  • To post as a guest, your comment is unpublished.
    MrBain · 2 years ago
    Hello,

    Would you kindly help me with the below?

    I am trying to use the below code, however, whenever I use the Range("A" & xCell.Row) to add a cell value in the Subject or Body of the email it blanks out the whole thing, if I only introduce "Text" it works fine.

    Column A has cells that get their value from another workbook through a simple formula (=IF('[TestWorkbook.xlsm]Sheet1'!C2="1",'[TestWorkbook.xlsm]Sheet1'!A2,"")). Would this cause the issue, seeing how cells in A:A get their value through a formula rather than manually imputed values?

    Code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range
    On Error Resume Next
    If (Target.Count > 1) Then Exit Sub
    Set xRg = Intersect(Target, Range("L:L"))
    If xRg Is Nothing Then Exit Sub
    If UCase(Target.Value) = "P" Then
    Call Mail_small_Text_Outlook(Target)
    End If
    End Sub
    Sub Mail_small_Text_Outlook(ByVal xCell As Range)
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hello Team" & vbNewLine & vbNewLine & _
    "Case " & Range("A" & xCell.Row) & " has been read and acknowledged"
    On Error Resume Next
    With xOutMail
    .To = "Email Address1; Email Address2; Email Address3; Email Address4; Email Address5"
    .CC = ""
    .BCC = ""
    .Subject = "Case " & Range("A" & xCell.Row)
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub


    Thank you!
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @Stu Hi,
    Sorry can't help you with that. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @glennwensley42@gmail.com Hi,
    For applying the code to a range of cells in a column, please change the specified cell D7 to the range of cells such as B2:B10.
  • To post as a guest, your comment is unpublished.
    glennwensley42@gmail.com · 2 years ago
    This is great for a single cell, but I would suggest a more useful function would be to have multiple instances where cells (within a range of cells on the same column) reach the same specific value that generates an automated email. However, Supposing you have a column with a range B2:B10, and want to generate an auto-email when a specific value (say the number 4) occurs anywhere within the column? Thus, if the value of 4 is achieved in Cell B3 and B7. Your code will generate an auto-email at B3 and B7, when what we really want is to generate an auto-email at B3, and then later, when B7 reaches the same value of 4, another email is automatically sent, but not one for B3. The instance for B3 is in the past and an email has been automatically sent for that instance. We don't want another email to be auto-generated for it when the value of 4 is reached further down the ranged column.

    What line of code would achieve this?
  • To post as a guest, your comment is unpublished.
    Stu · 2 years ago
    Hi, is it possible to get the email to send based on the result of and IF function in the spreadsheet? (=IF(D7>E1,TRUE,FALSE). At the moment, I can't see how to get it working that way.
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @cisraels Hi,
    The below VBA code can help you. Thank you for your comment.

    Dim xRg As Range
    'Update by Extendoffice 2018/3/7
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    Set xRg = Intersect(Range("D7"), Target)
    If xRg Is Nothing Then Exit Sub
    If Target.Value = "test" Then
    Call Mail_small_Text_Outlook
    End If
    End Sub
    Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
    "This is line 1" & vbNewLine & _
    "This is line 2"
    On Error Resume Next
    With xOutMail
    .To = "Email Address"
    .CC = ""
    .BCC = ""
    .Subject = "send by cell value test"
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub
  • To post as a guest, your comment is unpublished.
    Joe · 2 years ago
    Hi I want to show in the email what is the content of D7
  • To post as a guest, your comment is unpublished.
    cisraels · 2 years ago
    The VBA Script provided works for NUMERIC values entered:

    If IsNumeric(Target.Value) And Target.Value > 200 Then

    Can this be changed to allow TEXT??
  • To post as a guest, your comment is unpublished.
    motd614@gmail.com · 2 years ago
    Hello-I have tried so hard to get the code above to work for me, and I cannot. I went through all of the replies and tried to combine code, but I have been unsuccessful. I need to send an email reminder for uncompleted task. Column A has the task, Column B has the status. I need to send emails for every status that is TRUE. In the message I want it to include the uncompleted task. The messages go to different emails that are located in column C, BUT I only want to send one email per month. Someone please HELP.
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @Louis Hi Louis,
    If you want to make it (<200 AND save), here you need to apply three VBA codes in your workbook.

    1. Please insert a Module and rename it as "UpdataVBA_2", then copy below VBA code 1 into the Module window.

    VBA code 1.
    Public mBolSendMail As Boolean

    Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
    "This is line 1" & vbNewLine & _
    "This is line 2"
    On Error Resume Next
    With xOutMail
    .To = "Email Address"
    .CC = ""
    .BCC = ""
    .Subject = "send by cell value test"
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub

    2 Copy the below VBA code 2 into the ThisWorkbook code window

    VBA code 2:
    Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    If UpdataVBA_2.mBolSendMail Then
    Call Mail_small_Text_Outlook
    End If
    mBolSendMail = False
    End Sub

    Private Sub Workbook_Open()
    UpdataVBA.mBolSendMail = False
    End Sub

    3. Now you need to copy the following code into the worksheet code window your specific cell locates in.
    VBA code 3:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xPRg As Range
    Dim xRRg As Range
    On Error Resume Next
    Set xRRg = Range("D7")
    If Target.Cells.count > 1 Then Exit Sub
    Set xRg = Intersect(xRRg, Target)
    If xRg Is Nothing Then
    Set xPRg = xRRg.Precedents
    Set xRg = Intersect(xPRg, Target)
    If xRg Is Nothing Then Exit Sub
    End If
    If IsNumeric(xRRg) And xRRg.Value < 200 Then
    UpdataVBA_2.mBolSendMail = True
    'Call Mail_small_Text_Outlook
    End If
    End Sub
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @mathieumelissa Hi Melissa,
    Please change "D7" to "D:D" in the above VBA code if you want to work with all the cells in column D. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    April · 2 years ago
    Hi,

    What would the code look like if I am trying to send a reminder to myself that an expiry date is approaching (a month before official date). I have conditional formatting on the column (G2:G32) to highlight when the due date is a month away. How do I incorporate that into a VBA code to send to my e-mail as a reminder?
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @BSS Hi,
    Please try the method in this article. Hope it can help.
    How to send email if due date has been met in Excel?
    https://www.extendoffice.com/documents/excel/4664-excel-send-email-if-due-date-has-been-met.html
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @Kim Hi Kim,
    Sorry I am not sure I got your question. Would be nice if you could provide screenshot of what you are trying to do.
    Or you can post any question about Excel to our forum: https://www.extendoffice.com/forum.html to get more Excel supports from our professional or other Excel fans.
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @hiba Sorry can’t help you with this, welcome to post any question about Excel to our forum: https://www.extendoffice.com/forum.html. You will get more Excel supports from our professional or other Excel fans.
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @Jarvis Head Sorry can’t help you with this, welcome to post any question about Excel to our forum: https://www.extendoffice.com/forum.html. You will get more Excel supports from our professional or other Excel fans.
  • To post as a guest, your comment is unpublished.
    mathieumelissa · 2 years ago
    Hi, I am very new to VBA and when I copy and paste this I have managed to get it to work with my expiry dates but it only works for 1 cell. Is there a way for this to work with all the cells in a column.


    thanks
    Melissa
  • To post as a guest, your comment is unpublished.
    BSS · 2 years ago
    Hi
    I am fairly new to formatting on spreadsheets and am currently working on a spreadsheet that has multiple expiry dates over several columns and rows (I have already put in place conditional formatting to highlight in red when date has expired) However I was wondering if is possible to create either a report or alert of when the dates are due to expire?
    Any thoughts are gratefully received
  • To post as a guest, your comment is unpublished.
    Ian · 2 years ago
    Hello!


    I'm new to VBA, so this may be a dumb question. I'm trying to use this code to send automatic email alerts when inventory gets below a numerical value that is specific to each cell. I've been able to get it to work for one cell, and now I'm trying to repeat the process for other cells in my sheet. (i.e. When D7 get's below 2 it send out an email. But when D8 gets below 4 I'd like it to send out a different email.)However, copying and pasting the code and changing the specified cell does not work. What can I do to solve this problem? Thanks!
  • To post as a guest, your comment is unpublished.
    Kim · 2 years ago
    I have a code to autogenerate an email if an change is made to cells e11:a33. If I make several changes, I'd only like it to autogenerate the email once. How can I do this? Thanks,


    Dim xRg As Range
    'Update by Extendoffice 2018/3/7
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Determine if change was made to cells E11:E33
    If Not Intersect(Target, Range("E11:E33")) Is Nothing Then
    Call Mail_small_Text_Outlook
    End If
    End Sub
    Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "LAC team," & vbNewLine & vbNewLine & _
    "This LAC Event Management Macro for _ has a status update. Please review. Thanks."
    On Error Resume Next
    With xOutMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "Event Planning Update"
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0

    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub
  • To post as a guest, your comment is unpublished.
    hiba · 2 years ago
    I have a list of email addresses in excel that i need to send emails to The subject and body are in cells besides the email address and i have the script below but i need to send 100 emails per 1 hour how i can please your support

    Sub SendEm()
    Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    Set Mail_Object = CreateObject("Outlook.Application")
    For i = 2 To lr
    With Mail_Object.CreateItem(o)
    .Subject = Range("B" & i).Value
    .To = Range("A" & i).Value

    .Body = Range("C" & i).Value
    .attachments.Add (Sheets("Sheet1").Range("H" & i).Text)
    .attachments.Add (Sheets("Sheet1").Range("I" & i).Text)
    .attachments.Add (Sheets("Sheet1").Range("J" & i).Text)
    .attachments.Add (Sheets("Sheet1").Range("K" & i).Text)
    .Send

    '.display 'disable display and enable send to send automatically
    End With
    Next i
    MsgBox "E-mail successfully sent", 64
    Application.DisplayAlerts = False
    Set Mail_Object = Nothing
    End Sub
  • To post as a guest, your comment is unpublished.
    hiba · 2 years ago
    I have a list of email addresses in excel that i need to send emails to The subject and body are in cells besides the email address and i have the script below but i need to send 100 emails per 1 hour how i can please your support

    Sub SendEm()
    Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    Set Mail_Object = CreateObject("Outlook.Application")
    For i = 2 To lr
    With Mail_Object.CreateItem(o)
    .Subject = Range("B" & i).Value
    .To = Range("A" & i).Value

    .Body = Range("C" & i).Value
    .attachments.Add (Sheets("Sheet1").Range("H" & i).Text)
    .attachments.Add (Sheets("Sheet1").Range("I" & i).Text)
    .attachments.Add (Sheets("Sheet1").Range("J" & i).Text)
    .attachments.Add (Sheets("Sheet1").Range("K" & i).Text)
    .Send

    '.display 'disable display and enable send to send automatically
    End With
    Next i
    MsgBox "E-mail successfully sent", 64
    Application.DisplayAlerts = False
    Set Mail_Object = Nothing
    End Sub
  • To post as a guest, your comment is unpublished.
    Akbar · 2 years ago
    I have a requirement to send over 30 email. I have two sheet wherein in sheet1 the data is there and I create 30 individual worksheet with name.

    Below are conditions:-


    1. Each workbook will be send as a separate email.
    2. Each email will have different recipient for To, which is there in H7 and CC
    3. Each email will have same subject based on workbook/worksheet name.
    4. Each email will have same body based on worksheet Name & month.


    Now, a table can be made in excel which will list all points from 1-4 in different columns as a master data table. I can achieve this part.


    Can there be a macro which can read this table and create separate emails by adding attachments from a path?
  • To post as a guest, your comment is unpublished.
    Louis · 2 years ago
    Hi


    The email is working. The problem i have is that i would like to send the mail ones it is saved and the value is < 200
    if value is > 200 then no email should go out.
    i have 10 values to change and every time i change a value the calculated value sends a mail
    so i would like to make it ( <200 AND save ) then email should be generated.


    Thanks
  • To post as a guest, your comment is unpublished.
    xami4p@gmail.com · 2 years ago
    @crystal Hi Crystal,
    Thank you for the code. I was trying to use your code to send an email notification. The challenge is however, I have values in column F7 to F300 (some of the cells are blank in column F) which are formulas calculating the remaining days to certain deadlines. So, everyday the values in column F will decrease by 1. I would like to get a notification email where there are only 7 days left to the deadlines. Also, in the xMailBody, I would like to have texts referencing from the excel spreadsheet (like the project name which is going to be due and in how many days). Can this reminder email work even when excel is not running?
    I would appreciate very much your time if you kindly help me to solve the issue.
    Regards,
    Rahman
  • To post as a guest, your comment is unpublished.
    Nitol · 2 years ago
    @crystal It is not working for me as the value in D7 is a result of a formual. What if cell D7 contains a formula, e.g. D7 =2*120? It still meets the condition but nothing is happening. Please help
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @S.Balasubramanian Hi,
    Please try the below VBA code, hope it can help. Thanks for your comment.

    Dim xRg As Range
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    For Each xRg In Range("E2:E5")
    If CInt(xRg.Value) > 0 Then
    Call Mail_small_Text_Outlook
    End If
    Next
    End Sub
    Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Dim xIntR As Integer
    xIntR = xRg.Row
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
    "ID Order no. :" & Range("B" & xIntR).Value & vbNewLine & _
    "Part :" & Range("C" & xIntR).Value & vbNewLine & _
    "Qty :" & Range("D" & xIntR).Value & vbNewLine
    On Error Resume Next
    With xOutMail
    .To = Range("A" & xIntR).Value
    .CC = ""
    .BCC = ""
    .Subject = "send by cell value test"
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub
  • To post as a guest, your comment is unpublished.
    Jarvis Head · 2 years ago
    I have a worksheet where 60 different values could change, prompting 5 different emails. How do I write the code to look at each individual change and then send the corresponding email? Thanks for the assistance
  • To post as a guest, your comment is unpublished.
    S.Balasubramanian · 2 years ago
    Vendor ID Order no Part qty pending
    abc@gmail.com 6542684 apple 78 45
    abc@gmail.com 8786854 orange 45 0
    def@gmail.com 651654 Letter 1245 148
    def@gmail.com 898882 cover 547 65

    from the above excel sheet data

    i have to send mail to abc@gmail.com and def@gmail.com with their respective line

    condition is "pending" value should be greater than 0

    Guide me how to do it
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @Kennedy Hi Kennedy,
    The following VBA code can help you solve the problem. Please don't forget to replace the "Email Address" with the recipient's email address in the code. Thank you.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRgPre As Range
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    Set xRg = Range("D7")
    Set xRgPre = xRg.Precedents
    If xRg.Value > 200 Then
    If Target.Address = xRg.Address Then
    Call Mail_small_Text_Outlook
    ElseIf (Not xRgPre Is Nothing) And (Intersect(Target, xRgPre).Address = Target.Address) Then
    Call Mail_small_Text_Outlook
    End If
    End If
    End Sub
    Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
    "This is line 1" & vbNewLine & _
    "This is line 2"
    On Error Resume Next
    With xOutMail
    .To = "Email Address"
    .CC = ""
    .BCC = ""
    .Subject = "send by cell value test"
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub
  • To post as a guest, your comment is unpublished.
    Kennedy · 2 years ago
    I have the code running correctly for when a cell within a range of cells drops below a 1.The problem is that the cells I am referencing use a formula. And when a value that is part of the formula is changed and the cell referenced in the code drops below 1, an email is not sent. The email will only be prompted when the cell itself is changed. How do I code for this?


    Thanks
  • To post as a guest, your comment is unpublished.
    wu · 2 years ago
    Hi.

    I am wondering whether it is possible to tailor the first line of each email. For example, in my excel sheet I have a column with title such as Mr., a column with last name such as Black.
    I want to send out emails automatically with the first line: Dear Mr. Black
    for the others the first line will be accordingly dear ms. White....etc..


    Will that be possible to change the wording in the body of the email. That is my issue here.


    Thanks a lot
  • To post as a guest, your comment is unpublished.
    · 2 years ago
    I have a requirement to send over 50 email. Below are conditions:-


    1. Each workbook will be send as a separate email.
    2. Each email will have different recipient for To, CC and BCC.
    3. Each email will have different subject based on workbook/worksheet name.
    4. Each email will have different body based on date & subject.


    Now, a table can be made in excel which will list all points from 1-4 in different columns as a master data table. I can achieve this part.


    Can there be a macro which can read this table and create separate emails by adding attachments from a path?


    This will be a great help and save tremendous amount of time.
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @justin.p.patterson@gmail.com Hi Patterson,
    The below VBA code can help you solve the problem. Please have a try. Thank you for your comment.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range
    On Error Resume Next
    If (Target.Count > 1) Then Exit Sub
    Set xRg = Intersect(Target, Range("Z:Z"))
    If xRg Is Nothing Then Exit Sub
    If UCase(Target.Value) = "HOT" Then
    Call Mail_small_Text_Outlook(Target)
    End If
    End Sub
    Sub Mail_small_Text_Outlook(ByVal xCell As Range)
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hello Team" & vbNewLine & vbNewLine & _
    "Lot " & Range("D" & xCell.Row) & "'s Priority has changed to HOT, please prioritize this lot."
    On Error Resume Next
    With xOutMail
    .To = "Email Address1; Email Address2; Email Address3; Email Address4; Email Address5"
    .CC = ""
    .BCC = ""
    .Subject = "Lot number is " & Range("D" & xCell.Row)
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @Vignesh Hi Vignesh,
    The following VBA code can help you solve the problem. Thank you for your comment.

    Dim xRg As Range
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    Set xRg = Intersect(Range("D7"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value > 200 Then
    Call Mail_small_Text_Outlook
    End If
    End Sub
    Sub Mail_small_Text_Outlook()
    Dim I, J As Long
    Dim xRg As Range
    Dim xStr As String
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    Set xRg = Range("A1:G5")
    For I = 1 To xRg.Rows.Count
    For J = 1 To xRg.Columns.Count
    xStr = xStr & xRg.Rows(I).Cells(J) & " "
    Next
    xStr = xStr & vbNewLine
    Next
    xMailBody = "Hi there" & vbNewLine & vbNewLine & xStr
    On Error Resume Next
    With xOutMail
    .To = "Email Address"
    .CC = ""
    .BCC = ""
    .Subject = "send by cell value test"
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @Pavel Hello Pavel,
    Would you explain it in more details? I don't really get your point, sorry about that.
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @fifi Hello,
    There are two articles:
    1. How to send email with multiple attachments attached in Excel?
    https://www.extendoffice.com/documents/excel/4719-excel-macro-send-multiple-attachment.html

    2. How to send an email at specific time of a day in Excel?
    https://www.extendoffice.com/documents/excel/5028-excel-send-email-at-specific-time.html

    You can gather the VBA codes in these two articles to solve your problem. Hope I can help.
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @Jacob Good day,
    You need to place the code into the worksheet's code window.
    Open the Microsoft Visual Basic for Applications window, double click the sheet name in the left pane to open the code editor.
  • To post as a guest, your comment is unpublished.
    Jacob · 2 years ago
    @crystal Where exactly do we insert this code?
  • To post as a guest, your comment is unpublished.
    justin.p.patterson@gmail.com · 2 years ago
    I have a worksheet that has about 20 columns of data, but I only have two that I want to focus on here. Column D is lot number and Column Z is priority. I would like excel to send and email when Column Z has a priority of “HOT” and send an email including the associated lot number in Column D. It will either be HOT or blank so nothing needs to be done unless it is HOT. I would like the Email to go to about 5 people and say something along the lines of “Hello Team, Lot “xxxx’s” Priority has changed to HOT, please prioritize this lot.


    Is it possible to have it look up the lot number associated to the priority and place it in the text like that. If not maybe have the subject of the email be the associated lot number and then have the text just be as it was without the lot number.
  • To post as a guest, your comment is unpublished.
    Vignesh · 2 years ago
    I am trying to mail the content which is present in A1:G5 columns in excel ,but its not working. Can you help me with that?
  • To post as a guest, your comment is unpublished.
    Vetrak · 2 years ago
    I would like to ask if it is possible to send one email when closing the application - each change means a large number of emails.


    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If (Not Intersect(Target, Range("B2:B9")) Is Nothing) And (Target.Value < 3) Then
    Call Mail_small_Text_Outlook
    End If
    End Sub
    Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Ahoj," & vbNewLine & vbNewLine & _
    "xxx." & vbNewLine & _
    ""
    On Error Resume Next
    With xOutMail
    .To = "xxx"
    .CC = ""
    .BCC = ""
    .Subject = "xxx"
    .Body = xMailBody
    .Send 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub


    Thank you
  • To post as a guest, your comment is unpublished.
    Pavel · 2 years ago
    Hello
    I would like to ask if it is possible to send one email when closing the application - each change means a large number of emails.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If (Not Intersect(Target, Range("B2:B9")) Is Nothing) And (Target.Value < 3) Then
    Call Mail_small_Text_Outlook
    End If
    End Sub
    Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Ahoj," & vbNewLine & vbNewLine & _
    "XXX." & vbNewLine & _
    ""
    On Error Resume Next
    With xOutMail
    .To = "1234"
    .CC = ""
    .BCC = ""
    .Subject = "XXX"
    .Body = xMailBody
    .Send 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub