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.

Heya,

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)
  Next

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

thisworkbook.save

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"
.Send
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??

Thanks

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

Hi richadj4,

While executing the above program i am getting a compile error for this code "End(xlUp)" . Can you help me to solve the error message.

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.