VBA code to open specific email and copy the email body to excel and save it.

  • Re: VBA code to open specific email and copy the email body to excel and save it.


    Firstly I assume its now working ?

    a) what do you mean by "fix" the font and font size? You can definatley set the font and font size to whatever you like. Currently code is moving the information to columnA, so something like

    thisworkbook.sheets(1).columns(1).font.name = "Arial"
    thisworkbook.sheets(1).columns(1).font.size = 12

    b) yes, currently the reason that it doesn't is each line is set to go to the next blank cell so it is actually putting the blank lines in, it just overwrites them with the next line. to get around this, you need another dim line

    dim rstart as range

    and replace the existing "for j" loop with

    set rstart = thisworkbook.sheets(1).cells(65000,1).end(xlup).offset(1,0)
      For j = 0 To UBound(abody)
        'For each item in the array (i.e. each line) add the line to the first empty cell in column A of sheet1
        rstart.Offset(j+1, 0).Value = abody(j)

    this will find the next blank line at the start of the loop, and then go down one line for each array element.

    c) Not....... easily. You can set this macro to run automatically at specific times, but whenever it runs you will get the security warning about another application accessing your emails, which you will have to "OK". because of this, you will need human interaction here to get past this prompt. Although there are ways around this, I can't tell you about them :) This forum has strict (and in my opinion very worthwhile) rules about assisting people to bypass the security settings in Microsoft products, and this is one of them. Although I'm completely confident your goal is legitimate, this is a public forum, and the same information could easily be used to circumvent security settings in a non-legitimate application.

    d) sure, and the end you need something like

  • Re: VBA code to open specific email and copy the email body to excel and save it.

    Hi richadj4,

    Thanks for your quick reply. :) Ya, the code works lik wonders. Some stupid question i wanted to ask, How can i set this macro to run automatically at specific times?? Clicking just 'ok' will be fine with me since tis code already help me out by saving my time opening and closing the excel everyday.. haha.. i feel lik a child who just started to use microsoft products.. Thanks for all ur help.
    Best Regards,

  • Re: VBA code to open specific email and copy the email body to excel and save it.


    The easiest way to do this is to set the macro to run when the workbook opens. do this by adding the sub name to the workbook open event.

    Now whenever the document opens, it'll run this task.

    You can now use scheduled tasks in windows to set your document to open at a certain time each day. This is very different in different versions of windows, so I'd recommend googling it, but it's pretty straightforward.

    Now at whatever time of day you have chose this document will open and the macro will begin to run.

    As stated when the macro begins, you'll get the security warning, so you'll have to click the okay button.

  • Re: VBA code to open specific email and copy the email body to excel and save it.

    Hey Guys..

    I've few additions to the initial query. I've done the few modifications to the original code provided by Attilas... The goal is to forward those emails (to someone) which found in the sent folder against a specific criteria.

    Sub GetFromInbox()

    Dim olApp As Outlook.Application
    Dim olNs As Outlook.Namespace
    Dim olFldr As Outlook.MAPIFolder
    Dim olItms As Outlook.Items
    Dim olMail As Variant
    Dim i As Long

    Dim NewMailItem As Variant

    Set olApp = New Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")
    Set olFldr = olNs.GetDefaultFolder(olFolderSentMail)
    Set olItms = olFldr.Items

    olItms.Sort "Subject"

    i = 1

    For Each olMail In olItms
    If InStr(olMail.Subject, "TEST MSG") > 0 Then

    Set NewMailItem = olMail

    With NewMailItem
    .To = "abx@xyz.com"
    End With
    i = i + 1
    End If
    Next olMail

    Set olFldr = Nothing
    Set olNs = Nothing
    Set olApp = Nothing

    End Sub

    The problem that I'm facing in the code is, it replaces the original email with the new one after forwarding it. I know, I'm doing something wrong, but not aware of it.

    Can someone help me to solve this problem??


  • Re: VBA code to open specific email and copy the email body to excel and save it.

    Please do not post questions in threads started by other members.

    Start your own thread, give it an accurate and concise title as it required by the rules here and explain your issue fully. If you think this thread can help clarify your issue, you can include a link to it by copying the URL from the address bar of your browser and pasting into your message.

    Thank you.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!