Results 1 to 14 of 14

Thread: Copy email body

  1. #1
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Does anybody know a way to copy the body of and email for the purpose of pasting it into excel. I'm trying to figure out how to do this by avoiding the alerts triggered by the outlook model gaurd and not using redemptions.

    I've tried saving the emails to see if I could import them into excel as text but still end up triggering the alerts. This seems like it should be so simple, such as just clicking on the Edit - select all, Edit - copy options from the toolbar. I've also tried send keys which for me at least dont't seem to work.

    I'm not sure if it's possible to take and email received in the inbox and set it to be forwarded and before sending, copy the body then. If that is, any examples of how to do so would be helpful.

    Any help with this would be appreciated. Thanks in advanced!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Have you tried ClickYes?

  3. #3
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='789760' date='19-Aug-2009 11:24']Have you tried ClickYes?[/quote]


    Perhaps I should have elaborated a little further. I have a stand alone pc that will serve as and automation kiosk that will be fully automated. No manual intervention should be required. The pc will run 24/7 and detect outlook emails received in the inbox of a shared mailbox and execute a specific script for each and every email received. If I'm triggereing alerts that require manual intervention, this will hold everything up. The pc can not be monitored 24/7 to click yes when needed.

    I have about 100+ emails received daily that require work to be perfomed. All but about 3 have attachments and I can get what is needed from the attachments. The 3 without attachments have data I need to extract from the body of the email. Without triggering any alerts, how can I copy the text from the body of the email and paste it somewhere else such as word, excel, notepad?????

  4. #4
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,585
    Thanks
    5
    Thanked 1,059 Times in 928 Posts
    [quote name='b0bito' post='789775' date='19-Aug-2009 11:19']Perhaps I should have elaborated a little further. I have a stand alone pc that will serve as and automation kiosk that will be fully automated. No manual intervention should be required. The pc will run 24/7 and detect outlook emails received in the inbox of a shared mailbox and execute a specific script for each and every email received. If I'm triggereing alerts that require manual intervention, this will hold everything up. The pc can not be monitored 24/7 to click yes when needed.

    I have about 100+ emails received daily that require work to be perfomed. All but about 3 have attachments and I can get what is needed from the attachments. The 3 without attachments have data I need to extract from the body of the email. Without triggering any alerts, how can I copy the text from the body of the email and paste it somewhere else such as word, excel, notepad?????[/quote]

    Please read the information at the ClickYes link. It appears to do what you want - eliminate the alerts, run in the background, not require user input.

    Joe
    Joe

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='b0bito' post='789757' date='19-Aug-2009 08:18']Does anybody know a way to copy the body of an email for the purpose of pasting it into excel. I'm trying to figure out how to do this by avoiding the alerts triggered by the outlook model guard and not using Redemption.

    I'm not sure if it's possible to take and email received in the inbox and set it to be forwarded and before sending, copy the body then.[/quote]
    It should be easy to trigger this by Rule (or the ItemAdd event). Do you have any code written now?

    I can select the body of an email then copy and paste it into an Excel cell without triggering the alerts. The pseudo code with both Apps instanced shouldn't be more than:

    Excel.ActiveWorkbook.ActiveCell.Value = Outlook.ActiveInspector.CurrentItem.Body

    but in an unattended situation you probably want a Rule to act on the incoming message, something like Post 785667.

    It's very likely that the forwarding of the message is triggering the security warning, and perhaps a Rule triggering a script will avoid that, but if not, ClickYes is what you want.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='JohnBF' post='789796' date='19-Aug-2009 13:38']It should be easy to trigger this by Rule (or the ItemAdd event). Do you have any code written now?
    I can select the body of an email then copy and paste it into an Excel cell without triggering the alerts. The pseudo code with both Apps instanced shouldn't be more than:

    Excel.ActiveWorkbook.ActiveCell.Value = Outlook.ActiveInspector.CurrentItem.Body

    but in an unattended situation you probably want a Rule to act on the incoming message, something like Post 785667.

    It's very likely that the forwarding of the message is triggering the security warning, and perhaps a Rule triggering a script will avoid that, but if not, ClickYes is what you want.[/quote]
    Unfortunately I'm unable to use Click yes at my work. First the firewall's block access to the site, secondly security in place prevents the installation of it.

    Below is the code I have so far. I've indicated where the alert is triggered. Any help using the line you provided
    "Excel.ActiveWorkbook.ActiveCell.Value = Outlook.ActiveInspector.CurrentItem.Body"
    would be appreciated.

    [codebox]Public Sub GetSummaryReport()

    Dim olApp As Outlook.Application
    Dim olNS As Outlook.NameSpace
    Dim olFldr As Object
    Dim olMail As Outlook.MailItem

    eDate = Format(Date, "m/d/yyyy")
    Subj = "Summary Report"
    E_Flag = False

    If olApp Is Nothing Then
    Set olApp = CreateObject("Outlook.Application")
    End If
    Set olNS = olApp.GetNamespace("MAPI")
    Set olFldr = olNS.GetDefaultFolder(6)

    For Each olMail In olFldr.Items
    If E_Flag = True Then Exit For
    RDate = Format(olMail.ReceivedTime, "m/d/yyyy")
    If RDate = eDate Then
    If olMail.Subject = Subj Then
    olMail.Display
    EM_Body = olMail.Body ' Alert is triggered here
    End If
    End If
    Next olMail

    Set olApp = Nothing
    Set olNS = Nothing
    Set olFldr = Nothing
    Set olMail = Nothing

    End Sub[/codebox]

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    So you can't use ClickYes and you don't want to use Redemption. I fear you're stuck then.

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='b0bito' post='790771' date='26-Aug-2009 08:46']I'm unable to use Click yes at my work. First the firewall's block access to the site, secondly security in place prevents the installation of it.

    Below is the code I have so far. I've indicated where the alert is triggered. Any help using the line you provided "Excel.ActiveWorkbook.ActiveCell.Value = Outlook.ActiveInspector.CurrentItem.Body" would be appreciated.[/quote]
    Hans is probably correct, but it may be possible to avoid the prompts by using CDO, which coding I have less experience with.

    Might be while before I can help on this due to other commitments, but first a clarification and two questions:
    1. If this is to be triggered by the arrival of a message, the code has to run from Outlook, and in order to be triggered from a Rule your code needs some restructuring.
    2. Are you sure that you want to save the body in Excel? It will be simpler to save the message body, or even the entire message, to a file folder.
    3. In your original post you stated that the code would only run if there is no attachment to the message, or did I misunderstand that? If that's the case, are you running code for that now, and can you post it?
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='JohnBF' post='790785' date='26-Aug-2009 17:04']Hans is probably correct, but it may be possible to avoid the prompts by using CDO, which coding I have less experience with.[/quote]
    B0bito doesn't want to use Outlook Redemption, so I assume he doesn't want to use CDO either (Redemption uses RDO, a modified version of CDO).

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='HansV' post='790787' date='26-Aug-2009 09:12']B0bito doesn't want to use Outlook Redemption, so I assume he doesn't want to use CDO either (Redemption uses RDO, a modified version of CDO).[/quote]
    You may be right, though B0bito may already have CDO available as a Library to reference; some IS departments leave it available since they consider the possibility of it being used very remote.

    (And I just discovered a problem on my personal system, look for a post later in VB-VBA.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This routine would be triggered when new mail arrives in the inbox using the "This Outlook session - Private Sub Application_NewMail(). I don't know that I'm needing to have it attached to a rule though, but perhaps I'm misunderstanding what your meaning by attached to a rule.

    The Click yes and redemptions unfortunalty are out as my work limits access to the site where click yes is. Unfortunatly it also limits the right to install redemptions or click yes even though I have them copied on a thumb drive and did attempt to install them.

    I'm not concerned so much as to where I copy or save the body of the email to. I'm only concerned at this point with trying to find a way I can get to it without triggering and outlook alert. This way it will allow my scripts to run by themselves without intervention since we get automated emails at all hours that I need to pull data from.

    Basiclly, the email entitled "Summary Report" has a couple of numbers I need to pull from it that are in the body of the email(One item count and one dollar amount). If this means saving the body as a file that I can get to through a text file, Excel or another office app, that is fine.

    John BF - to your 3rd question. I do have another section of code that doesn't need to get at the body of the email. It's very similar to the code I posted earlier but it just grabs the attachement out of the email, saves it to the Environ("Temp") file. Once saved, the attachments at this point may be a note pad file that is imported into Excel to get to the numbers there or the other attachments are already excel based and I can just manipulate those as needed to get what I need from them.

    On the code I posted earlier, after the If statement that validates the subject is equal to subj (Summary Report). This is inserted to get to the attachments.


    ' ------------------------------------------------------------------------------------
    ' This section is inserted at the top near olApp As Outlook.Application
    Dim olAtt As Object
    Dim TempFile As String
    TempFile = Environ$("temp") & "\" ' Set temp file location
    '-------------------------------------------------------------------------------------

    For Each olAtt In olMail.Attachments
    olAtt.SaveAsFile TempFile & olAtt.FileName
    Next olAtt



    Perhaps I'm a bit confused on the CDO. I thought initially that CDO was only for sending emails, not so much reading them. I do have the CDO library available and I'm open to using it if there is a way.

    I do appreciate you reading my post and providing your thoughts and help as I work towards a solution. Thanks so much! Let me know if more details are needed. This isn't something I'm in a hurry to do but would like to finish it in the next couple weeks if possible. Thanks again!!

  12. #12
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Does Microsoft offer a different solution for this, either through "trusting" code in certain locations or by signing code? (Maybe it's not possible to sign the general VBA project file, only an add-in.)

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You *can* sign the code in the general VBA project in Outlook, but that doesn't stop the warning. The only workarounds are:
    - Use CDO/RDO (or Outlook Redemption, which uses RDO). This is considered safe because it can't be scripted.
    - If you're on Exchange, the Exchange admin can turn off the warnings at the server level.
    - Use ClickYes.

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='b0bito' post='790815' date='26-Aug-2009 11:59']I thought initially that CDO was only for sending emails, not so much reading them. I do have the CDO library available and I'm open to using it if there is a way.[/quote]
    CDO = Collaborative Data Objects, amongst other things it replicates some of the MAPI capabilities of Outlook, but it has no user interface. I'll see if there's a way to forward a message via CDO which avoids the security guard prompt. (Any other Lounge volunteers please chip in!)

    The NewMail event is reported to be unreliable as it can be overwhelmed by a high message volume. The ItemAdd (to a Folder such as the Inbox) Event is better, and hooking the code to a Rule using the "Run A Script" option has advantages in maintenance.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •