نصيحة: اللغات الأخرى مترجمة من قبل Google. يمكنك زيارة English نسخة من هذا الرابط.
تسجيل الدخول
x
or
x
x
التسجيل
x

or

كيفية إرسال البريد الإلكتروني تلقائيا بناء على قيمة الخلية في إكسيل؟

لنفترض أنك تريد إرسال بريد إلكتروني من خلال أوتلوك إلى مستلم معين استنادا إلى قيمة خلية محددة في إكسيل. على سبيل المثال، عندما تكون قيمة دسنومكس الخلية في ورقة عمل أكبر من شنومك، ثم يتم إنشاء البريد الإلكتروني تلقائيا. تقدم هذه المقالة طريقة فبا لك لحل هذه المشكلة بسرعة.

إرسال البريد الإلكتروني تلقائيا استنادا إلى قيمة الخلية مع التعليمات البرمجية فبا


قم بإرسال البريد الإلكتروني من خلال Outlook بسهولة استنادًا إلى حقول القائمة البريدية التي تم إنشاؤها في Excel:

الـ إرسال رسائل البريد الإلكتروني فائدة كوتولس ل إكسيل يساعد المستخدمين على إرسال البريد الإلكتروني من خلال Outlook استنادًا إلى قائمة بريدية تم إنشاؤها في Excel. قم بتنزيل الدرب المجاني الكامل ل 60-day من Kutools for Excel الآن!

  • إنشاء قائمة بريدية مع جميع حقول البريد المطلوبة وتحديد كل منهم.
  • تمكين وظيفة إرسال رسائل البريد الإلكتروني وملء هيئة البريد الإلكتروني.
  • إرسال عبر أوتلوك.

كوتولس ل إكسيل: مع أكثر من شنومكس مفيد اكسل الإضافية، مجانا في محاولة مع عدم وجود قيود في أيام شنومكس. تنزيل النسخة التجريبية المجانية الآن!

علامة تبويب Office تمكين التحرير والتصفح في Tabbed ، وجعل عملك أسهل بكثير ...
Kutools for Excel يحل معظم مشاكلك ، ويزيد من إنتاجيتك بنسبة 80٪
  • إعادة استخدام أي شيء: أضف الصيغ الأكثر استخدامًا أو تعقيدًا والمخططات وأي شيء آخر إلى مفضلاتك ، ثم أعد استخدامها سريعًا في المستقبل.
  • أكثر من ميزات نص 20: استخراج الرقم من سلسلة النص ؛ استخراج أو إزالة جزء من النصوص ؛ تحويل الأرقام والعملات إلى الكلمات الإنجليزية ...
  • دمج الأدوات: العديد من المصنفات والأوراق في واحد ؛ دمج خلايا / صفوف / أعمدة متعددة دون فقدان البيانات ؛ دمج الصفوف المكررة ومجموع ...
  • أدوات الانقسام: تقسيم البيانات إلى أوراق متعددة بناءً على القيمة ؛ مصنف واحد إلى ملفات Excel أو PDF أو CSV متعددة ؛ عمود واحد إلى أعمدة متعددة ...
  • لصق الطفر الصفوف المخفية / المصفاة ؛ عدد ومبلغ بواسطة لون الخلفية. إنشاء قائمة بريدية و إرسال رسائل البريد الإلكتروني حسب قيمة الخلية...
  • فلتر سوبر: إنشاء مخططات تصفية متقدمة وتطبيقها على أي أوراق ؛ فرز حسب الأسبوع ، اليوم ، التردد وغير ذلك ؛ تصفية / فلترة بواسطة جريئة ، الصيغ ، التعليق ...
  • أكثر من ميزات 300 القوية ؛ يعمل مع Office 2007-2019 و 365 ؛ يدعم جميع اللغات ؛ سهولة النشر في مؤسستك أو مؤسستك.

إرسال البريد الإلكتروني تلقائيا استنادا إلى قيمة الخلية مع التعليمات البرمجية فبا

يرجى القيام على النحو التالي لإرسال بريد إلكتروني استنادا إلى قيمة الخلية في إكسيل.

1. في ورقة العمل تحتاج إلى إرسال البريد الإلكتروني استنادا إلى قيمة الخلية (هنا يقول دسنومكس الخلية)، انقر بزر الماوس الأيمن فوق علامة التبويب ورقة، وحدد عرض الرمز من قائمة السياق. انظر لقطة الشاشة:

2. في ظهرت ميكروسوفت فيسوال باسيك للتطبيقات نافذة، الرجاء نسخ ولصق التعليمات البرمجية فبا أدناه في نافذة التعليمات البرمجية ورقة.

رمز فبا: إرسال البريد الإلكتروني من خلال أوتلوك استنادا إلى قيمة الخلية في إكسيل

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. في التعليمات البرمجية فبا، D7 و قيمة> شنومك هي الخلية وقيمة الخلية سوف ترسل البريد الإلكتروني على أساس.

2. يرجى تغيير هيئة البريد الإلكتروني كما تريد xMailBody في التعليمات البرمجية.

3. استبدل عنوان البريد الإلكتروني بعنوان البريد الإلكتروني للمستلم في السطر .To = "عنوان البريد الإلكتروني".

4. وحدد المستلمين سيسي و بسك كما تحتاج إليها .CC = "" و بسك = "" أقسام.

5. وأخيرا تغيير موضوع البريد الإلكتروني في السطر .Subject = "إرسال بواسطة اختبار قيمة الخلية".

3. اضغط على قديم + Q مفاتيح معا لإغلاق ميكروسوفت فيسوال باسيك للتطبيقات نافذة.

من الآن فصاعدا، عندما تكون القيمة التي تدخلها في الخلية دسنومكس أكبر من شنومكس، سيتم إنشاء بريد إلكتروني مع المستلمين المحدد والجسم تلقائيا في أوتلوك. يمكنك النقر فوق إرسال زر لإرسال هذا البريد الإلكتروني. انظر لقطة الشاشة:

الملاحظات:

1. تعمل التعليمات البرمجية فبا فقط عند استخدام أوتلوك كبرنامج البريد الإلكتروني الخاص بك.

2. إذا كانت البيانات المدخلة في الخلية دسنومكس هي قيمة نصية، سيتم برزت نافذة البريد الإلكتروني خارج كذلك.


تبويب أوفيس - الاستعراض المبوب ، وتحرير ، وإدارة المصنفات في Excel:

يقوم Office Tab بدمج الواجهة المبوبة كما تظهر في متصفحات الويب مثل Google Chrome والإصدارات الجديدة من Internet Explorer و Firefox إلى Microsoft Excel. وستكون أداة لتوفير الوقت ولا يمكن استبدالها في عملك. انظر أدناه العرض التوضيحي:

انقر على نسخة تجريبية مجانية من تبويب أوفيس!

تبويب Office لـ Excel


مقالات ذات صلة:


Kutools for Excel يحل معظم مشاكلك ، ويزيد من إنتاجيتك بنسبة 80٪

  • إعادة استخدام: إدراج بسرعة الصيغ المعقدة ، الرسوم البيانية وأي شيء استخدمته من قبل ؛ تشفير الخلايا مع كلمة المرور إنشاء قائمة بريدية وإرسال رسائل البريد الإلكتروني ...
  • سوبر الفورمولا بار (بسهولة تحرير أسطر متعددة من النص والصيغة) ؛ تخطيط القراءة (بسهولة قراءة وتحرير أعداد كبيرة من الخلايا) ؛ لصق على المدى المصفى...
  • دمج الخلايا / الصفوف / الأعمدة دون فقدان البيانات ؛ انقسام خلايا المحتوى ؛ الجمع بين تكرار الصفوف / الأعمدة... منع الخلايا المكررة. مقارنة النطاقات...
  • حدد تكرار أو فريد الصفوف. حدد صفوف فارغة (جميع الخلايا فارغة) ؛ سوبر البحث والعثور غامض في العديد من المصنفات ؛ اختيار عشوائي ...
  • نسخة طبق الأصل خلايا متعددة دون تغيير مرجع الصيغة ؛ إنشاء المراجع تلقائيًا إلى أوراق متعددة. إدراج الرصاصات، مربعات الاختيار والمزيد ...
  • استخراج النص، إضافة نص ، حذف حسب الموضع ، إزالة الفضاء. إنشاء وطباعة مجاميع ترحيل الصفحات ؛ تحويل بين محتوى الخلايا والتعليقات...
  • سوبر تصفية (حفظ وتطبيق مخططات التصفية على أوراق أخرى) ؛ تصنيف متقدم حسب الشهر / الأسبوع / اليوم ، التردد وأكثر ؛ فلتر خاص بواسطة جريئة ، مائل ...
  • الجمع بين المصنفات وأوراق العمل. دمج الجداول على أساس الأعمدة الرئيسية ؛ تقسيم البيانات إلى أوراق متعددة; دفعة تحويل XLS ، XLSX وقوات الدفاع الشعبي...
  • أكثر من ميزات 300 القوية. يدعم Office / Excel 2007-2019 و 365. يدعم جميع اللغات. سهولة النشر في مؤسستك أو مؤسستك. ميزات كاملة النسخة التجريبية المجانية من 30.
علامة التبويب kte 201905

علامة تبويب Office توفر واجهة مبوبة لـ Office ، واجعل عملك أسهل بكثير

  • تمكين تحرير علامات التبويب والقراءة في Word و Excel و PowerPointوالناشر والوصول ، Visio ومشروع.
  • افتح مستندات متعددة وقم بإنشائها في علامات تبويب جديدة لنفس النافذة ، وليس في نوافذ جديدة.
  • يزيد إنتاجيتك بنسبة 50٪ ، ويقلل مئات نقرات الماوس لك كل يوم!
أسفل مكتبه
Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    Emanuel Kumar · 1 months ago
    I need help with Excel to send an automatic email using outlook when a cell value is changed in the file.

    Question . Sheet name "Attrition Report" Column L in the file has the list of RAG status, So, when one of the manager will choose the options (Green, Red, Amber) for possible attrition cases. When the cell value changes to Red or Amber ; excel should automatically send an email ONLY when it changes to RED or Amber. The body of the email should show employee name which is in Cell range "B" and the RAG status which is in Range "L".
    I put the code but i guess it's incorrect.

    Any help on this will be much appreciated.

    Thanks and Regards

    Emanuel Prasanna Kumar

    The code i currently have. is.

    _______________

    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("L2:L100")
    If CInt(xRg.Value) = "Red" And "Amber" 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 & _
    "Emp Name. :" & Range("B" & xIntR).Value & vbNewLine & _
    "RAG Status :" & Range("L" & xIntR).Value & vbNewLine & _
    "Factor :" & Range("K" & xIntR).Value & vbNewLine
    On Error Resume Next
    With xOutMail
    .To = "Emanuel.Kumar@infovision.com"
    .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.
    Rajeshwari Agale · 1 months ago
    How can I display the Sheet name and list of cells which has value more than 200 in the email content?
  • To post as a guest, your comment is unpublished.
    Claire Campbell · 1 months ago
    Hello,
    How do I get excel to send an email automatically when the worksheet is opened based on a cell value?
  • To post as a guest, your comment is unpublished.
    Claire · 1 months ago
    Hello,

    How can i get excel to send an email automatically when the workbook is opened based on a cell value, instead of when the cell value is changed?
  • To post as a guest, your comment is unpublished.
    joseph · 1 months ago
    I want to add different email address per row, but when I change one row, the entire worksheet changes. How can I limit the changes only to one row each per one email account?
  • To post as a guest, your comment is unpublished.
    shreyas cse · 1 months ago
    Hi All,
    Can someone help me to figure out the below
    I have an Excel sheet with loads of worksheets in it.
    Data is entered in 3 worksheets on daily basis and information from these 3 sheets is sent out to mailing list on every Sunday (data accumulated from Last Sunday to Saturday in those 3 work sheets).
    This XL sheet is stored in share point.
  • To post as a guest, your comment is unpublished.
    Katie · 1 months ago
    Hi Crystal,
    Thank you for this code. It is extremely helpful. I am trying to add a few things to it and was wondering if you or someone following this post could help.

    How can you add Cell Text from the same Range (row) to the Mail Body?
    How can you get the code to send an email for every Cell in the Range that is over an amount?

    Thank you so much!
  • To post as a guest, your comment is unpublished.
    Rob · 2 months ago
    Hi Crystal,
    Thank you for your extremely useful posts! I have the VBA code working perfectly for our purposes, except for one problem - when I open the workbook or make a change, each email is created twice. What can be done to ensure each email is only created once?

    Many thanks, Rob

    My current code:

    Dim xRg As Range
    'Update by Extendoffice 2019/8/2
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    Set xRg = Intersect(Range("A1"), 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 = "Dear " & Range("C3") & "," & vbNewLine & vbNewLine & _
    "I am writing to inform you that the Form D filing for " & Range("C2") & " is due on " & Range("C4") & ". All securities offerings that rely on the exemptions set forth under Regulation D are required to file a Form D every year for as long as the offering is open." & vbNewLine & vbNewLine & _
    "It is advised that " & Range("C2") & " should maintain its Form D filing if it continues to offer securities in reliance on Regulation D. Maintaining an up-to-date Form D is important in ensuring compliance with federal and state securities regulations." & vbNewLine & vbNewLine & _
    "Please let us know at your earliest convenience if you wish to arrange the renewal of the Form D." & vbNewLine & vbNewLine & _
    "Thank you," & vbNewLine
    On Error Resume Next
    With xOutMail
    .To = Range("C5")
    .CC = "t.kim@geracillp.com"
    .BCC = ""
    .Subject = "Form D filing notice for " & Range("C2")
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub

    Private Sub Worksheet_Calculate()
    Dim xI As Integer
    Dim xRg As Range
    Set xRg = Range("A1")
    On Error GoTo Err01
    xI = Int(xRg.Value)
    If xI = 1 Then
    Call Mail_small_Text_Outlook
    End If
    Err01:
    End Sub
    • To post as a guest, your comment is unpublished.
      Rob · 2 months ago
      Actually, more to the point - how do I make it so that only one email is created when I open the workbook, but not just when I make a change to it? Currently changing any cell results in a new email being created.

      Please help with this last hurdle, I'm so close!
  • To post as a guest, your comment is unpublished.
    Viji · 2 months ago
    How to insert the images in email body
  • To post as a guest, your comment is unpublished.
    Edward · 2 months ago
    I Keep Getting this:

    Run-time error '429':

    ActiveX component can't create object

    I use apple and don't have outlook... is there a way to make this work without outlook.. and

    instead Mail Version 11.5 (3445.9.1) apple
  • To post as a guest, your comment is unpublished.
    Ganesh · 2 months ago
    Thank you for posting valuable and important VBA code. I am new in VBA coding and trying to modify your VBA with editing Mail Body;

    ...
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
    "This is line 1" & vbNewLine & _
    "This is line 2"
    On Error Resume Next
    ...

    How to add Cell Text Ex. text from A7, B7 & C7 related to D7 into Mail Body instead of other text. Could someone please guide me. Thank you in advance.
  • To post as a guest, your comment is unpublished.
    ricardo suarez · 2 months ago
    I have another question, how do you write in VBA so instead of "If IsNumeric(Target.Value) And Target.Value > 200 Then" (detecting a value greater than 200 it detects any text input..... for example in cell D7 you write any words (not number but words) it calls out the email ! please respond
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Hi,
      Please replace the line "If IsNumeric(Target.Value) And Target.Value > 200 Then" with "If Target.Value = "test" Then"
      "test" is the specific word you will cal out the email based on, please change it to your own word.
  • To post as a guest, your comment is unpublished.
    ricardo suarez · 2 months ago
    Hi

    I got a question, on excel I have a column with several emails written (they are formulated), for example A36 is example1@hotmail.com, A37 is example2@hotmail.com, A38 is example3@hotmail.com, I want that the part of the programming VBA To: Range (A36:A38) so they send the email to each of those wroten emails, the thing is that I put it like this To: Range (A36:A38) but it does not work, am I doing it wrong?
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Hi ricardo suarez,
      The below VBA code can solve your problem. Please have a try and thank you for your comment.

      Dim xRg As Range
      'Update by Extendoffice 2019/8/14
      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
      For Each cell In Range("A36:A38")
      If cell.Value Like "?*@?*.?*" Then
      strto = strto & cell.Value & ";"
      End If
      Next cell
      If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)
      Application.ScreenUpdating = False
      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 = strto
      .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.
    Anisa · 2 months ago
    Hi


    My vba code works fine, even the email part to outlook. I'm quite chuffed as I am a novice at vba. I created an automated process to create an invoice and email in pdf.


    However the next step is I want to include a status on the excel spreadsheet, which is my database to say "complete" or "sent". I'm thinking maybe an IF formula? I don't want to use a message box. Basically I just want to keep record that for each creditor the email has been sent.


    How would I do that in VBA


    Thanks for your help:)
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Hi Anisa,
      Sorry can't help you with that. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Roxiann · 2 months ago
    Hi. I am trying to use your code and I get the email but I want the email address I have in cell E159 to pop up as the email address. I also want to Display a cell value in the middle of my email. I have tried several ways and none seem to work. I cannot use the kudotools app. I only have excel and the vba.
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Hi Roxiann,
      Supposing you want to send email to address in cell E159, and display value of cell A1 in your email body, you can try the below VBA code. Thanks 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 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 & _
      Range("A1") & vbNewLine & _
      "This is line 2"
      On Error Resume Next
      With xOutMail
      .To = Range("E159")
      .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.
    Otoniel · 3 months ago
    Hi Crystal, thanks for the last answer, i got a quick question, how can i add the cell value automatically to the mailbody
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Or just include this line Range("D7") & vbNewLine & _ in the xMailBody line.

      xMailBody = "Hi there" & vbNewLine & vbNewLine & _
      "This is line 1" & vbNewLine & _
      Range("D7") & vbNewLine & _
      "This is line 2"
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Hi Otoniel,
      Supposing the cell D7 is in the worksheet named "Sheet2", please change the xMailBody line in above code to
      xMailBody = "The value of D7 is" & ThisWorkbook.Sheets("Sheet2").Cells(7, 4).Value

      Don't forget to change the sheet name and the cell number as you need (here Cells(7,4) represents cell D7 which locationg in row 7 column 4).
  • To post as a guest, your comment is unpublished.
    Paras · 4 months ago
    This is great but just the last step is not completing my request. Could you please update so it automatically sends out the email, without clicking send.
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Hi Paras,
      In the code, please replace the .Display line with .Send. Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    jak kincaid · 4 months ago
    I am having a similar issue as has been brought up, the VBA works with manually values. I have 2 workbooks, I hope to pull data from workbook 2 into workbook1. Workbook 1 then subtracts 2 numbers, one of which is pulled from workbook2 (via using formula of =value in cell in workbook 2). Then if the difference is less than a given number, I hope to send an email. When I manually enter the number the VBA works great. When I use the = value of cell in workbook 2, VBA does not run. I feel so close but still out of arms length. Any help is greatly appreciated. Thanks much.
  • To post as a guest, your comment is unpublished.
    Otoniel · 4 months ago
    Hello is it possible, that instead of popping up the email.

    Can it be configured to automatically send the mail, without the need to send it manually?
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Hi Otoniel,
      In the code, please replace the .Display line with .Send. Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Toph42 · 5 months ago
    Is there was a way to prompt the email by cells that are populated by a formula? This seems to only work when populating the cells manually. Thanks!
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Good day,
      The below VBA code will do you a favor.

      Dim xRg As Range
      'Update by Extendoffice 2019/8/2
      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

      Private Sub Worksheet_Calculate()
      Dim xI As Integer
      Dim xRg As Range
      Set xRg = Range("D7")
      On Error GoTo Err01
      xI = Int(xRg.Value)
      If xI > 200 Then
      Call Mail_small_Text_Outlook
      End If
      Err01:
      End Sub
  • To post as a guest, your comment is unpublished.
    Kayden · 6 months ago
    I've set this VBA to range instead of a single cell. However, I'd like to specify which cell value has triggered this email to be sent. Let say, Value on D3 is greater than 200, that triggered this email message from range (D1:E20). How can I embed this code into VBA, so I can add this to email subject? any help would be greatly appreciated!
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Hi Kayden,
      If you want to specify a range of cells rather than single cell, the below VBA code can help. But I don't really understand your last question about "so I can add this to email subject". Thanks for your comment.

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Cells.Count > 1 Then Exit Sub
      If Intersect(Target, Range("D1:E20")) = Target 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 = ""
      .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.
        KAYDEN · 3 months ago
        Hello Crystal,

        Thank you very much for your response! I'd like to elaborate an issue I have. I have a range of cells as "target". When email is generated, on Subject line, I'd like to add a cell that triggered this email. For instance, Qty of Mango fell below target value of 200, then I'd like to add Please order "Mango" on the subject line. Since the target is a range, I'd want some type of formula that can refer to cell B2 (Mango - name of the fruit column). I hope you could help me out on this! Thanks once again!
        • To post as a guest, your comment is unpublished.
          crystal · 3 months ago
          HI KAYDEN,
          Do you mean the subject line is dynamically changed based on the target value of the Qty column? When Qty of Mango fell below target value of 200, send email with subject line "Please order Mango (refers to cell B2)"; When Qty of another fruit fell below target value of 200, send email with subject line of the corresponding fruit name?
          • To post as a guest, your comment is unpublished.
            Rodrigo · 2 months ago
            I think what he's also trying to say (beside the dynamics in the subject of the email) is that by setting a range of cells that can trigger the email, you wont know what that change was and where.

            With a single cell instead of a range you'll know exactly where to look once you get the email or you can even add the value that triggered the email in the email body, as I read above by specifying the single cell you're trying to track.

            But when you specify a range, how can you add in the body of the email the value and cell or column that started the whole action? How do I know what and where the change happened?
          • To post as a guest, your comment is unpublished.
            KAYDEN · 2 months ago
            Dear Crystal,

            Yes, you are correct. I'd like to have a dynamic subject line to reflect the name of fruit! Sorry I was ambiguous on my asking, but you understood perfectly! Do you think there's a code that I can use to make this work? Thank you again for your help!
  • To post as a guest, your comment is unpublished.
    Dmytro · 6 months ago
    Hi! How should I copy/paste the code to get it to work? (after copying/pasting with line number and line text it shows up "invalid outside procedure"; copying/pasting only text lines without line numbers it does nothing after changing the cell value).
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Hi Dmytro,
      Sorry for the inconvenience. You just need to copy the text lines without the line numbers. The code only works when the criterion met (In this case, when value in cell D7 is changed to more than 200, the code will work).
      Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Justin · 6 months ago
    so when you say "Automatically Send Email Based On Cell Value" you really mean make outlook pop up so you can manually press the send button? or am I missing something?
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Hi Justin,
      Replace the .Display with .Send in the code will automtically send the email as soon as the criterion met.
      Popping up the email can help readers to test the code when following the steps.
      Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Sophie · 6 months 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.
      crystal · 3 months ago
      Hi Sophie,
      Please change the > 200 to =0 to make it work.Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Derek · 7 months 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 · 7 months 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 · 7 months 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 · 7 months 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.
    asint · 7 months ago
    how if there are several cells, each cell has a different value
  • To post as a guest, your comment is unpublished.
    Cris Bain · 8 months 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.
    Elysiumfire · 8 months 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.
      crystal · 8 months ago
      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.
    Stu · 9 months 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 · 8 months ago
      Hi,
      Sorry can't help you with that. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Joe · 9 months ago
    Hi I want to show in the email what is the content of D7
  • To post as a guest, your comment is unpublished.
    Cedric Israelsohn · 10 months 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.
      crystal · 9 months ago
      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.
    XsanmarX · 11 months 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.
    April · 11 months 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.
    Melissa · 1 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.
      crystal · 11 months ago
      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.
    BSS · 1 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 · 1 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 · 1 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.
      crystal · 11 months ago
      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.
    hiba · 1 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 · 1 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 · 1 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 · 1 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.
      crystal · 11 months ago
      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.
    Jarvis Head · 1 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 · 1 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 · 1 years ago
      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.
    Kennedy · 1 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.
      crystal · 1 years ago
      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.
        Zamiur Rahman · 1 years ago
        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.
    wu · 1 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.
    ExcelEnthusiast · 1 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.
    Justin Patterson · 1 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.
      crystal · 1 years ago
      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