Free Microsoft Excel 2013 Quick Reference

Auto generated email with text

Is it possible to have an email that is generated through a macro where if a cell is left blank nothing happens, but when a number is entered into the cell it will start a macro that generates an email to a set address, with a preset subject and in the body of the email it will pull data from specific cells?


Hi -

I am working on a Gantt chart template to be used by a workgroup, each member completing different tasks, each reliant on the previous task's completion. What I am looking to do is have an auto generated email sent to the person completing the next task when the previous task is completed. Also, the sheet will be saved, edited and resaved on a Sharepoint site.

For example, in the attached sheet, I would like an email to be generated when a date is entered in column H, Actual Complete Date, to let the person responsible for the next tast know that they can begin their task.

For now, those needing email notification is within one specific work group, but the template would have application in other areas, so understanding how to adjust who receives the emails would be valuable to me.

From my previous searches it looks like this will involve some work in VB, in which I have limited knowledge. But, I'm a quick learner and would appreciate any help/advice anyone would be able to give.

Thanks in advance,
Walt

Hi all, i have some code which creates and sends an email from excel but i'm having trouble generating the body text. i believe i need some sort of array but am unsure as i have no experiense with arrays.

i would basically like to go through {K12:K50} and search for critera {<=0.1} .When found count incidents {Incident = incident + 1} and add the values from columns C,D,E,K on incident row to a newline of email body. So the email body test would look like ...

Encountered Incidents = 5
Incident 1 : ColC.Value , ColD.Value , ColE.Value , ColK.Value
Incident 2 : ColC.Value , ColD.Value , ColE.Value , ColK.Value
Incident 3 : ColC.Value , ColD.Value , ColE.Value , ColK.Value
Incident 4 : ColC.Value , ColD.Value , ColE.Value , ColK.Value
Incident 5 : ColC.Value , ColD.Value , ColE.Value , ColK.Value

Any help would be greatly appreciated

I use a Macro that when run, will generate an email with the excel sheet attached. See attached example.

My two questions are:
- is it possible for the body of the email that is generated to have spaces within the text? So it shows more like the below:
"Good morning,

Please find today's forecast for Friday, May 07, 2010 attached.

Thank you,

FRC Treasury Team"

- Also, the colors in the spreadsheet change automatically when the macro attaches the sheet. Why is this? Is it possible to do something so that this does not happen?

Thank you,

Hi All,

I have a register of items stored in an excel file. Each of the items
has a different date associated with them and I would like to be able
to get Excel to generate emails (all to the same address) notifying
someone of the item number once the date is within X days.

I am fairly familiar with excel so I have no problem in writing
formulas which identify when the X (currently 30, but could change in
the future) days warning period has started. The problem is that
currently someone must open the workbook and examine the lookup page to
see if any actions are required. It would save a lot of time if I could
get the workbook to generate emails.

All help warmly received...

Cheers.

--
Phil P
------------------------------------------------------------------------
Phil P's Profile: http://www.excelforum.com/member.php...o&userid=35316
View this thread: http://www.excelforum.com/showthread...hreadid=550928

Hi All,

I have a register of items stored in an excel file. Each of the items has a different date associated with them and I would like to be able to get Excel to generate emails (all to the same address) notifying someone of the item number once the date is within X days.

I am fairly familiar with excel so I have no problem in writing formulas which identify when the X (currently 30, but could change in the future) days warning period has started. The problem is that currently someone must open the workbook and examine the lookup page to see if any actions are required. It would save a lot of time if I could get the workbook to generate emails.

All help warmly received...

Cheers.

hello, I was wondering how to imbed a hyperlink into the body of an automatically generated email.

Ideally the hyperlink would be based on a cell value to allow a user to cut and paste a file path

example filepath
P:Project Folderproject worksheet.xls

Here is the current autoemail VBA code I am using. So the filepath would be taken from
Sheet: Behind The Scenes D4

Private Sub SimplifiedEmail_Click()
Dim myOutlok As Object
Dim myMailItm As Object

Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
fName = ActiveWorkbook.Path & "" & ActiveWorkbook.Name

With otlNewMail
.To = Sheets("Behind The Scenes").Range("D2")
.Subject = Sheets("Behind The Scenes").Range("D3") & " " & Sheets("Behind The
Scenes").Range("E3")
.Body = Sheets("Behind The Scenes").Range("D5") & "     " & Sheets("Behind The
Scenes").Range("D6") & Chr(10) & Sheets("Behind The Scenes").Range("D7")
.Send
End With

otlApp.Quit

Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing

End Sub


Email HyperLink or MailTo HyperLink in Outlook 2007 Email Body

Our company has an auto-generated email with a mail-to hyperlink or email hyperlink to another email. The hyperlink works without any problems but with the installation of Outlook 2007 the hyperlink no longer. In order to open the hyperlink email, one has to open the original email, click Other Actions, then click View In Browser. Once the original email opens in the Internet Explorer window then clicking on the email hyperlink opens the embedded email. Is there a way to determine if there is a setting in Outlook 2007 that needs to be changed? Or is this a code redesign issue?

Appreciate your help on auto filling cells with texts conditionally in a loop by using only FORMULAS.
I have just started working in excel. I have spent good amount of time to fix it with help of formulas. But no luck.
If it can not be achieved with only formulas and if the issue needs macro coding, please do send me the piece of VBA code.

Example :
Sheet 1:
A1 = Houston
A2 - A7 = Empty cells
A8 = Total of sales in Houston
A9 = Dallas
A10 - A13 = Empty cells
A14 = Total of sales in Dallas
A15 = Austin
A16 - A18 = Empty cells
A19 = Total of sales in Austin

The data in sheet1 changes monthly.
Now the requirement is to get column A in sheet 2 with below changes dynamically.

Fill A2 to A7 with "Houston"
Fill A10 to A13 with Dallas
Fill A16 - A18 with "Austin"

Thanks in advance.
Rajani.

hi...i have a complaint database in excel. I have a userform to enter
complaints. I have written a code so that excel sends a notification
email automatically asv soon as user clicks on the "Submit" button on
the userform.

I want to customise this email subject & body with complaint details.
e.g. subject will say something like "Ä new complaint no 12345 has
been logged for ABC trading Company". The complaint number is entered
in colun A & Customer name is stored in column C of the worksheet
"ComplaintData".

I want the same also in the email body. ...something like : "Ä new
complaint no 12345 has been logged for ABC trading Company. Please log
on to Customer Complaint System to see details"

Also, I am wondering if I can change the sender name on these
auto-generated email...e.g. Ď would like the sender name to be
"Customer Complaint system" instead of my own name or the user's name
who is logging the complaint.

Is this possible??

Cheers

hi...i have a complaint database in excel. I have a userform to enter
complaints. I have written a code so that excel sends a notification
email automatically asv soon as user clicks on the "Submit" button on
the userform.

I want to customise this email subject & body with complaint details.
e=2Eg. subject will say something like "=C4 new complaint no 12345 has
been logged for ABC trading Company". The complaint number is entered
in colun A & Customer name is stored in column C of the worksheet
"ComplaintData".

I want the same also in the email body. ...something like : "=C4 new
complaint no 12345 has been logged for ABC trading Company. Please log
on to Customer Complaint System to see details"

Also, I am wondering if I can change the sender name on these
auto-generated email...e.g. =CF would like the sender name to be
"Customer Complaint system" instead of my own name or the user's name
who is logging the complaint.

Is this possible??

Cheers

Hi there

I am hoping you can help. I have created a user form and have used a thread in this forum to auto generate a unique number for each incident registered.

But when I have added it into my code and amended what I thought I should but it will not work and comes up with a compilation of errors. I have worksheets named 'Database', 'Sheet2' and 'LookupLists'. The pop up box is called 'TextBox1' and I also have a field in my userform called 'txtincidentnumber'.

I think I am getting confused and running myself in circles but cannot correct the errors.

Ultimately, when the 'register' button is pressed I want all data input to be saved to the spreadsheet and the unique identifier to be displayed in a pop up box that can then be closed by clicking 'Close'

Thank you for reading and considering.


	VB:
	
 cmdRegister_Click() 
    Dim iRow As Long 
    Dim ws As Worksheet 
    Set ws = Worksheets("Database") 
    Dim lists As Worksheet 
    Set lists = Worksheets("LookupLists") 
    Dim s2 As Worksheet 
    Set s2 = Worksheets("Sheet2") 
     'find first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _ 
    .End(xlUp).Offset(1, 0).Row 
    ws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = Me.txtIncidentNumber.Text 
    s2.Cells(1).Value = s2.Cells(1).Value + 1 
    Unload Me 
    Sub TextBox1_Change() 
        Sub TextBox1_Initialize() 
             
            Me.TextBox1.Text = Sheet2.Cells(1).Value + 1 
             
        End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


okay. i already had the how i wanted it everything works as follows.

i wrote the macros for the work order number to generate once the service order is opened and and then it has a few buttons one button is save and the other is create PDF.
when you press the save button the auto generate macro deletes itself and the button and it user cell data and the work order number to make the file name in which its saved that way once i do that and email it the button will not be there and the number will not change.. secondly i have a button that will automatically create a PDF the the same file name. once the button is pressed it deletes itself befor generating the PDF that way you don't see the button on the PDF. all works good oh and its set to save as 97-2003 file type.

heres the issue i was now asked to make to so that it has a work order number range. so right now is references a note made file on my root drive called settings and it can be any number if i open the file and type 400 and save it then open my service order template it will generate 401 and that number will be saved into the settings folder that way the next time it will be 402.

i want to make it so the user can put a starting and finishing number into the settings folder and write the macro so that when it gets to say the (current number = >(max number - 20) then let the user know its time for updated ticket numbers..

Workbook macro
Public Sub Workbook_Open()
        ThisWorkbook.Sheets(1).Range("H8").Value = NextSeqNumber
        Call DeleteWorkbookEventCode
    End Sub
Public Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long
        Const sDEFAULT_PATH As String = "c:"
        Const sDEFAULT_FNAME As String = "settings.txt"
        Dim nFileNumber As Long
        
        nFileNumber = FreeFile
        If sFileName = "" Then sFileName = sDEFAULT_FNAME
        If InStr(sFileName, Application.PathSeparator) = 0 Then _
            sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName
        If nSeqNumber = -1& Then
            If Dir(sFileName) <> "" Then
                Open sFileName For Input As nFileNumber
                Input #nFileNumber, nSeqNumber
                nSeqNumber = nSeqNumber + 1&
                Close nFileNumber
            Else
                nSeqNumber = 1&
            End If
        End If
        On Error GoTo PathError
        Open sFileName For Output As nFileNumber
        On Error GoTo 0
        Print #nFileNumber, nSeqNumber
        Close nFileNumber
        NextSeqNumber = nSeqNumber
        Exit Function
PathError:
        NextSeqNumber = -1&
    End Function
Sub Main2()
   Call DeleteButton2
   Call SvMe
   

End Sub
Sub DeleteButton1()
    Dim Btn As Object
     
    ActiveSheet.Shapes("Button 1 ").Delete
     
End Sub
Sub DeleteButton2()
    Dim Btn As Object
     
    ActiveSheet.Shapes("Button 2 ").Delete
     
End Sub

Sub DeleteWorkbookEventCode()

    With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule

            .DeleteLines 1, 33
            

    End With

                                

End Sub

Sub SvMe() 'Save filename as value of H8(ticket Number) plus Michael Plus The Value Of B8(Customer Name) Plus the current
date

    Dim newFile As String, fName As String, cName As String
    On Error GoTo Skip
    
    
    fName = Range("H8").Value
    cName = Range("B8").Value
    newFile = fName & " " & "Michael" & " " & cName & " " &
Format$(Date, "mm-dd-yyyy")
     
    ChDir _
   "C:Deleted for forum"
    ActiveWorkbook.SaveAs Filename:=newFile, FileFormat:=56
'Path must exist on  Drive
Skip:

End Sub

Work sheet code

Sub SavePDF()

     Dim newFile As String, fName As String, cName As String
     On Error GoTo ChDir2
     On Error GoTo ChDir3
    fName = Range("H8").Value
    cName = Range("B8").Value
    newFile = fName & " Michael " & cName & "" & Format$(Date, "mm-dd-yyyy")
    
    ChDir _
   "C:Deleted for forum"
    
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=newFile _
      , Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
        
        
ChDir2:

      ChDir _
    "C:Deleted for forum"
   
   
   
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=newFile _
      , Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    
ChDir3:

      ChDir _
    "C:Deleted for forum"
   
   
   
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=newFile _
      , Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    
    
    
End Sub


I have a column with text emails in it of the text form "person@website.com"
I wish to convert the entire row to hyperlinks to email ...

If I click on "HYPERLINK" and then select email it gives this hyperlink the
text name
of person@website.com and asks me to type in the email ... argh...

-- Gerry

Hi Folks,

I found the below code from this forum. Tried running it from excel and it successfully sends an email to Lotus Notes email, with no attachment.

However, in the excel file where I have put in this macro using forms toolbar, I would like to send one of the worksheets as a file attachment, together with the email.

Would be grateful if someone could guide me how to modify this code to achieve my aim.

Thanks alot,
Gon


	VB:
	
 LotusMail() 
     
     ' setting up various objects
    Dim Maildb As Object 
    Dim UserName As String 
    Dim MailDbName As String 
    Dim MailDoc As Object 
    Dim attachME As Object 
    Dim Session As Object 
    Dim EmbedObj1 As Object 
    Dim recipient As String 
    Dim ccRecipient As String 
    Dim bccRecipient As String 
    Dim subject As String 
    Dim bodytext As String 
    Dim Attachment1 As String 
     
     ' setting up all sending recipients
    recipient = ThisWorkbook.Worksheets("Data").Range("A1").Value 
     'ccRecipient = ThisWorkbook.Worksheets("Data").Range("A1").Value + ThisWorkbook.Worksheets("Data").Range("ac1").Value
     'bccRecipient = ""
    subject = "Claim File" 
    bodytext = "Please find Attached Claim File" 
     
     '// Lets check to see if form is filled in Min req =Recipient, Subject, Body Text
    If recipient = vbNullString Or subject = vbNullString Or bodytext = vbNullString Then 
        MsgBox "Recipient, Subject and or Body Text is NOT SET!", vbCritical + vbInformation 
        Exit Sub 
    End If 
     
     ' creating a notes session
    Set Session = CreateObject("Notes.NotesSession") 
    UserName = Session.UserName 
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf" 
    Set Maildb = Session.GETDATABASE("", MailDbName) 
     
    If Maildb.IsOpen  True Then 
        On Error Resume Next 
        Maildb.OPENMAIL 
    End If 
     
    Set MailDoc = Maildb.CreateDocument 
    MailDoc.Form = "Memo" 
     
     ' loading the lotus notes e-mail with the inputed data
    With MailDoc 
        .SendTo = recipient 
         '.copyto = ccRecipient
         '.blindcopyto = bccRecipient
        .subject = subject 
        .Body = bodytext 
    End With 
     
     ' saving message
    MailDoc.SaveMessageOnSend = True 
     
    Attachment1 = ThisWorkbook.Worksheets("Data").Range("ad1").Value 
    If Attachment1  "" Then 
        Set attachME = MailDoc.CreateRichTextItem("Attachment1") 
        Set EmbedObj1 = attachME.EmbedObject(1454, "", Attachment1, "Attachment") 
        MailDoc.CreateRichTextItem ("Attachment") 
    End If 
     
     ' send e-mail !!!!
    MailDoc.PostedDate = Now() 
     ' if error in attachment or name of recipients
    On Error Goto errorhandler1 
     
    MailDoc.Send 0, recipient 
     
    Set Maildb = Nothing 
    Set MailDoc = Nothing 
    Set attachME = Nothing 
    Set Session = Nothing 
    Set EmbedObj1 = Nothing 
     
     'Unload Me
    Exit Sub 
     ' setting up the error message
errorhandler1: 
    MsgBox "Incorrect name supplied or the attachment has not attached," & _ 
    "or your Lotus Notes has not opened correctly. Recommend you open up Lotus Notes" & _ 
    "to ensure the application runs correctly and that a vaild connection exists" 
     
    Set Maildb = Nothing 
    Set MailDoc = Nothing 
    Set attachME = Nothing 
    Set Session = Nothing 
    Set EmbedObj1 = Nothing 
     ' unloading the userform
     'Unload Me
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a spreadsheet which contains:-
Name and address in Cols A - G
Contact Price in Col I
Send Renewal Letter Date in Col K
Contact Finish Date in Col L

I am using Conditional Formatting on Col K it turns RED when Cell Value is
less than $L$3 (Todays Date). When this happends I would like to auto generate
a letter with the customers name and address at the top of the letter and the Contract Finish date and Contact price in the letter body.
I can do this manually via mail merge (using Word), but was wondering if if possible
to auto generate the letter(s), say once a week via a macro triggered by a button push? This then generates all the letters for customers where the Send Renewal Letter date is RED. Any Ideas?
Thanks eD

I have the VBA code for creating a Lotus Notes email from EXcel.

However, and I trust this is a simple question, I cannot break up the body section with line breaks.

The code is:

MailDoc.Body = _
"Deduction data has been processed for employees for the week commencing " & Range("a7").Value & ", totalling $" & Range("b5").Value & ". These deductions will be part of the next available payrun."

The email body text created is:

Deduction data has been processed for employees for the week commencing 13/03/2006, totalling $22814.5. These deductions will be part of the next available payrun.

I just need a line break as follows:

Deduction data has been processed for employees for the week commencing 13/03/2006, totalling $22814.5.

These deductions will be part of the next available payrun.

Thx!

I am hoping someone has a solution to auto filling data in a column which has changing data down the column.

What do i mean: to currently fill a column with the data in cell A1 i click and drag and then hit CTRL+D. What i want to do is fill the column with the data from A1 until i reach a cell further down with text, lets say cell A50, and have the data in cell A50 continue down until i reach the next cell containing data and continue this all the way down the column.

I would appreciate any help on this

Thanks

I was wondering if there was a way to get Excel to auto generate worksheets within a workbook with a certain naming convention.

For example, I have financial sheets that I use the Saturday of every week. So for this week the worksheet is named 0216 (February 16). This would continue till sometime in may, so it would need to correspond to the months, and the saturdays that each month fell on.

Thanks.

Need aid in copying certain cells with text/labels to clipboard to format a body of email to be sent via Yahoo webmail, so I can paste it where needed.

I did a couple of Excel VBA's back in 2000 or so at last job but haven't seen/used VBA since.
Have a spreadsheet with customer data, (one cust per row) and wish to create something like the following in clipboard (in this example, assuming row 2 is the selected row)...I'll only be sending out one or two of these per day so nothing fancy is needed, just a way to avoid retyping critical data that could get mistyped.

--------------------
A8

Rental details for 2008

Customer Name : A2
Unit nbr : A3
Nbr of weeks : A4
Balance due : A5
--------------------

I'm using Office 2000 still...I've done some searching but results found seem to complicated for my situation or not quite close enough for me to see how to apply it.

Could someone give me some pointers if this is easily done, and if so, how?
Thanks much!

Hello,
I'm new to this site and hoping that someone here can help me out. Here's a screenshot of my situation...

I would like to auto-generate column I based on criteria in columns A and C. The values to be auto-generated with are contained in column O. For example, A2 is equal to 5/7/07 and C2 is equal to 3...I2 should be auto-generated with the value 132 due to the matching of HOUR and DATE in columns L and M. L9 is equal to 3 and M9 is equal to 5/7/07...the corresponding value in O8 is equal to 132, which is the value to be generated in I2.

Any help is much appreciated...hopefully I was able to clearly depict my issue!

Thanks in advance.
~Josh

I need to email a list of attachments to a list of recipients. My list looks
like this
file email recipient
123file.xls

Generated email with excel attachement retains excel formatting when sent
from our UNIX SUN server, but the mso- directives seem to have no effect when
sent from our Win 2000 server. The email transmission text is identical.
There is no discernable difference in the email headers that are recevied.
Could the commands in the email be causing the problem?

Thx,
Steve

i am trying to create a workbook which the first page is the customer
information page. this is done... now i need to use the first two letters
from each cell containing the first and last names... now i need to add the
current date in ddmmyy format NO PUNCTUATION...followed by a dash and 0000
which will increase by 1 each time the name cells are filled in. i would
also like to add auto "save as" with this generated number as the name of the
work book. every customer gets a new work book for each job ( 1 cust / 10
jobs = 10 work books )

this is what i am looking for;

customer name is Will I. Twirk
he calls for service on 01/06/05
after confirming his interest we create a new work book for the new customer
I open the work book template
I add customers last name
I add cust first name
when i press tab next... i want the number to generate and show up as
TWWI010605-0001

can this happen??? HELLLPPPP!!!!!

I don't want to push my luck but..... it would be really nice if this number
could be sent to the invoice page to create the same number but with an I
just be4 the dash.

and if i have not figured by then how to do the same for the estimate page
with an E instead of an I ...... what the heck can you show that too???
pleeeeease heeeeellllp.

i really need to be able to track by name so this seems like it would really
suit my needs

I've always been told, "It never hurts to ask", so here I go.

I was wondering if it is possible (without Papal Intervention) to use Excel
to generate email via MS Outlook based upon worksheet data.

Specifically: I'm working on a task to record/track/inventory Purchase
Orders (PO) on my contract. My desire is to send email 120 days before the
PO ends to the individual(s) on the contract exercising authority over the
purchased items or services. [Email addresses of cognizant individuals and
renewal data info would be entered when the PO is approved and received, thus
setting the stage for renewal evolutions in the future.]

I'm not well-skilled by any means in Excel, but would prefer to use it if
possible to accomplish all that is required of me.

Thanks,
Jeff