Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-22-2014, 04:38 AM
QA_Compliance_Advisor QA_Compliance_Advisor is offline Using VBA/ Macro sending a email which is populated from data in Excel Windows 7 32bit Using VBA/ Macro sending a email which is populated from data in Excel Office 2007
Advanced Beginner
Using VBA/ Macro sending a email which is populated from data in Excel
 
Join Date: Jul 2014
Posts: 44
QA_Compliance_Advisor is on a distinguished road
Default Using VBA/ Macro sending a email which is populated from data in Excel

As a compliance department we have 11 stages that require to be completed before a document can be revised and published. It is time consuming entering this info manually from excel into outlook emailing.

Is there any way that I can use VBA/ Macro to allow an email for each stage with the populated data from excel spreadsheet. However, I do need the email in HTML format with an email with instructions how to deal with the stage being emailed.

Data I need populated in;

Subject: (1) Tracking Number, Document Number and Document Titles and Deadline

Body: Date Sent, Deadline, and the info of previous stages that have been completed.

All the info is on the same spreadsheet.



any help towards a solution would be awesome.

Last edited by QA_Compliance_Advisor; 07-22-2014 at 06:24 AM. Reason: poorly worded title
Reply With Quote
  #2  
Old 07-22-2014, 09:39 AM
BobBridges's Avatar
BobBridges BobBridges is offline Using VBA/ Macro sending a email which is populated from data in Excel Windows 7 64bit Using VBA/ Macro sending a email which is populated from data in Excel Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

QA, I've worked with VBA/Outlook before, and with HTML it's a bit of a pain but it absolutely can be done. In fact, maybe you'll discover some things that make it less of a pain that it is for me—that is, maybe I've been doing it the hard way.

The general principle comes in two parts: a) There's an Outlook Application object just as there is an Excel Application object, and b) you can use either one in any VBA program regardless of whether that program is running in Excel or Outlook. (Actually you don't even have to be running in any Office app; you can use those objects just as well in VBScript. That's handy, for it means you can double-click on an icon in Windows and have it do all the work without even having to be in Excel-or-whatever.)

But here's where the pain comes in: If you've learned to write Excel macros, you'll probably remember a time when you had to struggle to understand what the Excel objects do. Not all those objects were intuitively obvious to you the first time you tried work with them. The same is true of Outlook; it's not like Excel at all, so even though the language is the same, the behavior of Outlook objects isn't easy to learn—or at least it hasn't been for me, so far.

Here's a snippet of code I wrote in MS Access to set up an HTML email and then display it for me to modify and send. I've added comments in hopes of making the broad outline easier to follow:
Code:
  Set ol = CreateObject("Outlook.Application") 'establishes the main Outlook object
  Set eml = ol.CreateItem(0) 'starts a new email (I think 0=olMailItem, the type)

  ' I've already got the To and CC addresses set up, and the subject comes from a form.
  eml.To = EmlsTo
  eml.CC = EmlsCC
  If rsSubj!Text <> "" Then eml.Subject = rsSubj!Text

  eml.Display 'makes the email visible to the user (that's me)

  ' The email starts out blank with just my signature.  But it only
  ' looks blank; there's really a lot of HTML code in there.  Here's
  ' where I try to find the right point at which to insert my text:
  Const StartPoint = "<div class=Section1>"
  pi = InStr(eml.HTMLBody, StartPoint)
  If pi = 0 Then
    ' some sort of error here; that HTML tag was supposed to be there
  Else
    pi = pi + Len(StartPoint)
    End If
  pi = pi + 2 'to account for the CrLf

  ' So pi is now the character position of the text to be inserted.  Now break
  ' up the text that's already there into Before and After:
  Dim BodyLeft, BodyMid, BodyRight
  BodyLeft = Left(eml.HTMLBody, pi - 1)
  BodyRight = Mid(eml.HTMLBody, pi)

  ' Assemble the text to be inserted.  Include the FAQ if the checkbox says so.
  BodyMid = Sals & rsBody!Text
  If rsBody!NclFaq Then BodyMid = BodyMid & _
    vbCrLf & vbCrLf & DLookup("Text", "Text", "Key='faq'")
  BodyMid = HTMLParagraph(BodyMid) 'turn plain text into HTML
  eml.HTMLBody = BodyLeft & BodyMid & BodyRight 'reassemble the message body

  ' Attach resume to a cold email
  If rsBody!NclResume Then eml.Attachments.Add _
    CurrentProject.Path & "\RHBridges.doc", olByValue

  eml.GetInspector.Activate ' bring the email window to the fore so the user can inspect and modify
Right in the middle there's a call to a routine I named HTMLParagraph; it takes plain text and puts HTML code around it. Maybe the details don't matter to you, but just in case, it looks like this:
Code:
' Wrap the necessary HTML code around a block of text.
Function HTMLParagraph(txt) As String
  Const ParA = vbCrLf & "<p class=Bob>"
  Const ParZ = "&nbsp;</p>" & vbCrLf

  ' First replace any CrLfs of the incoming plain text with blank HTML paragraphs
  pb = InStrRev(txt, vbCrLf)
  Do While pb > 0
    txt = Left(txt, pb - 1) & ParZ & ParA & Mid(txt, pb + 2)
    pb = InStrRev(txt, vbCrLf, pb)
    Loop

  HTMLParagraph = ParA & txt & ParZ
  End Function
Now, all this looks like a lot of work to me. Maybe to someone who automates VBA a lot, it doesn't seem so—or maybe such a person would sneer at my code, telling me there are much easier ways to do it. In any case, all this is supposed to demonstrate that yes, you absolutely can do it, and you don't even have to leave Excel to make it work. But you'll probably have to spend a lot of time experimenting and swearing at it before it actually does everything you want to. Either that or maybe you'll find a VBA/Outlook jock at your workplace who'll make it all go a lot easier for you.
Reply With Quote
  #3  
Old 07-22-2014, 10:30 AM
QA_Compliance_Advisor QA_Compliance_Advisor is offline Using VBA/ Macro sending a email which is populated from data in Excel Windows 7 32bit Using VBA/ Macro sending a email which is populated from data in Excel Office 2007
Advanced Beginner
Using VBA/ Macro sending a email which is populated from data in Excel
 
Join Date: Jul 2014
Posts: 44
QA_Compliance_Advisor is on a distinguished road
Default

Thanks, however, I do not think I have explained myself well.

The way we track changes to documents is through register in excel which has a tracking number per row per document. each row will have all info about the tracking number relating to the document.

I need to automate emails to be populated with data from the particualr row that relates to the tracking number. So if i wantt o send an email due to tracking number 001 reached stage 05 - i want o be able to send an email by either selecting the row of the tracking number or selecting the tracking number, i need the email to look along the row (like lookup) and start populating the data for the particular stage in the template email. I would prefer to have a separate code for each different stage and email.
Reply With Quote
  #4  
Old 07-22-2014, 11:04 AM
BobBridges's Avatar
BobBridges BobBridges is offline Using VBA/ Macro sending a email which is populated from data in Excel Windows 7 64bit Using VBA/ Macro sending a email which is populated from data in Excel Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Sure, I get it. I wasn't suggesting that my snippet of code would work for you; I was just showing you what it looks like to write code to create an email, and to populate it with data, from some other Office application. You asked "is it possible?", and I replied "yes, it is—and it looks something like this".

To meet your exact needs you'd have write your own macro. Some of us here would help you develop it, but I thought I'd show you what's involved.

It's probably just a matter of terminology, but let me correct one thing in your description:
Quote:
.... i need the email to look along the row (like lookup) and start populating the data for the particular stage....
The email wouldn't do it. Your macro would create the email, then pull the data from the relevant row and put it into the email. Probably that's what you meant; I just wanted to be sure.
Reply With Quote
  #5  
Old 07-23-2014, 02:58 AM
QA_Compliance_Advisor QA_Compliance_Advisor is offline Using VBA/ Macro sending a email which is populated from data in Excel Windows 7 32bit Using VBA/ Macro sending a email which is populated from data in Excel Office 2007
Advanced Beginner
Using VBA/ Macro sending a email which is populated from data in Excel
 
Join Date: Jul 2014
Posts: 44
QA_Compliance_Advisor is on a distinguished road
Default

yes you are correct, outlook is not the best application to use for this purpose, as the data needs to be pulled and then an email created to put that information in the email.
Reply With Quote
  #6  
Old 07-23-2014, 07:48 AM
BobBridges's Avatar
BobBridges BobBridges is offline Using VBA/ Macro sending a email which is populated from data in Excel Windows 7 64bit Using VBA/ Macro sending a email which is populated from data in Excel Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Technically you could write the macro to run in Outlook. Just as you can open Outlook objects in Excel, you can open Excel objects in Outlook. So if you did it in Outlook, your program would open the Excel workbook, ask the user to point out the desired row, pull in the data, create an email and send it.

I agree, though, it seems easier to do it in Excel. You're probably in Excel already, for one thing; and that way the user can, as you say, invoke the program by pointing to the correct row so that the program knows at the start where the data is, instead of having to ask.

So, do you want to start writing this macro with help? Or are you content at this point just to know it's possible, and start making plans for later?
Reply With Quote
  #7  
Old 07-23-2014, 07:51 AM
QA_Compliance_Advisor QA_Compliance_Advisor is offline Using VBA/ Macro sending a email which is populated from data in Excel Windows 7 32bit Using VBA/ Macro sending a email which is populated from data in Excel Office 2007
Advanced Beginner
Using VBA/ Macro sending a email which is populated from data in Excel
 
Join Date: Jul 2014
Posts: 44
QA_Compliance_Advisor is on a distinguished road
Default

I think I need to start planning and writing the code, cause this is driving me crazy with the amount of data to be inserted into an email.

would be awesome if you could assist.
Reply With Quote
  #8  
Old 07-23-2014, 08:14 AM
BobBridges's Avatar
BobBridges BobBridges is offline Using VBA/ Macro sending a email which is populated from data in Excel Windows 7 64bit Using VBA/ Macro sending a email which is populated from data in Excel Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

So let's start here: Tell me about the way the data must look in the email. Is it possible to just paste it in there, once you have it in the right form? What I mean is this: Suppose we write a macro to assemble all the data from Excel and set it up in a single location; can you just cut and paste it from there? Or does it have to be in some particular format in the email, like in rows and columns or something?

That wouldn't be the final form of the macro, you understand. I'm thinking about writing the macro to pull the data from Excel, thus saving you some of the work. Once that's working correctly, a next step would be to enhance the program to set up the email. But we don't have to do it all at once; we can do it a piece at a time and get benefits from it as we progress.
Reply With Quote
  #9  
Old 07-25-2014, 06:28 AM
QA_Compliance_Advisor QA_Compliance_Advisor is offline Using VBA/ Macro sending a email which is populated from data in Excel Windows 7 32bit Using VBA/ Macro sending a email which is populated from data in Excel Office 2007
Advanced Beginner
Using VBA/ Macro sending a email which is populated from data in Excel
 
Join Date: Jul 2014
Posts: 44
QA_Compliance_Advisor is on a distinguished road
Default

Is it possible to have the info populated into a template fro outlook or will the html have to be written into the code of the macro?
Reply With Quote
  #10  
Old 07-25-2014, 07:00 AM
BobBridges's Avatar
BobBridges BobBridges is offline Using VBA/ Macro sending a email which is populated from data in Excel Windows 7 64bit Using VBA/ Macro sending a email which is populated from data in Excel Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

I've been assuming that the email would have a lot of text that is the same all the time, and our macro will plug a few values in here and there. Like this:
Quote:
On __________, the weekly job JTDA1001 ran and determined that there are __ unclaimed dingbats in the M35 inventory and __ blarticks. The next run will be on __________.
The macro's responsibility will to pull the data from Excel, pull up a copy of the above template and find the blank places to plug the data into.

That would probably involve no HTML tags in the macro—or at most, maybe you'd want to insert a tag or two around special data, say <b> and </b> around any value greater than the norm. We can decide as we go along, I should think. But mostly the HTML would already be in the template and we're just stuffing our values in among it.
Reply With Quote
Reply

Tags
email, populate, vba

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
sending data from UserForm of existing excel file to a new excel file saltlakebuffalo Excel Programming 0 02-13-2014 10:55 PM
[VBA, Interop, 2007] Email Populated from Access cannot be Spellchecked DepricatedZero Outlook 0 06-17-2013 08:58 AM
Using VBA/ Macro sending a email which is populated from data in Excel Macro: Exporting Data to a LEGIBLE Excel Spreadsheet jeffcoleky Word VBA 6 05-08-2012 08:24 AM
Using VBA/ Macro sending a email which is populated from data in Excel Importing data from excel using a macro soma104 Word 1 04-14-2011 05:10 PM
Sending email from Excel coolpeter86 Excel 1 02-10-2011 07:54 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:50 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft