Outlook/Excel macro? 3

[jisoo22]

Hello people =)

Ok, here's the situation: I've created a macro within MS Excel 2000 that will take all the information from cells and toss them into an email (and then automatically send them off to a pre-destined email). Here's my problem, I have to figure out a macro to have that info from the email get put back into the same cells. If you can't figure it out, it's bascially sending info from an excel form through email and having it go back into the same form upon receipt. Does anyone have an idea of how to start something like this? I can't find very much that will, even finding something as simple as what the object is for Excel (ie. createObject("Outlook.Application) blah blah...

Thanks!
Jisoo22

 

[jisoo22]

Hmmm...

I think I know where to start but I don't know the proper statements. Does anyone know what statement to use to extract a single line from the body of an outlook email and stick it into a specified cell in Excel?

Thanks,
Jisoo22

 

[jonscott8]

Set oOutlook = CreateObject("Outlook.Application&quot
Set oNameSpace = oOutlook.GetNamespace("MAPI&quot
Set oFolder = oNameSpace.GetDefaultFolder(olFolderInbox)
Set oMail = oFolder.Items.Find("[Subject]='My Subject'&quot
sBody = oMail.Body
Application.ActiveCell.Value = sBody

AFA extracting a single line, you can parse sBody for each occurrence of chr(13).

The outlook vba programming help file is named vbaoutl9.chm for Outlook 2000 and vbaoutl8.hlp for 98(i think). Search your hard drive. Jon Hawkins

 

[jisoo22]

Great, thanks =) One question though, after I parse sBody for the bits of text that I want, how do I assign them to cells in an excel spreadsheet? I.E. Cell "A2"?

Jisoo22

 

[jisoo22]

This'll probably help a little bit. Here's some of the code I have so far:

Sub Autpen()

Dim myOutlook As Outlook.Application
Dim ns As Outlook.NameSpace
Dim fld As Outlook.MAPIFolder
Dim itms As Outlook.Items
Dim itm As Object
Set olOutlook = CreateObject("Outlook.Application&quot
Set ns = olOutlook.GetNamespace("MAPI&quot
Set itms = ns.GetDefaultFolder(olFolderInbox).Items
Set myMail = itms.Item

For Each myMail In itms
x = myMail.Body
a = InStr(x, "Claim Type: &quot
astr = Mid(x, a + 11, b)
b = InStr(x, "Dealer #: &quot

That's about all I can piece together so far. If anyone else has helpful suggestions, don't hesitate to shoot =)

Thanks!
Jisoo22

 

[jonscott8]

sLine="Whatever"
Application.ActiveSheet.Range("C2&quot.Value = sLine

The excel vba programming help file is named vbaxl9.chm for Excel 2000 and vbaxl8.hlp for 98(i think). Search your hard drive. Jon Hawkins

 

[jisoo22]

Hi again,

I'm having problems opening up an item in the Outlook inbox from Excel. Can anyone tell me what's wrong with my code? I'm guessing the error is somewhere in this section of it:

Sub Item_Open()
Dim myOutlook As Outlook.Application
Dim ns As Outlook.NameSpace
Dim fld As Outlook.MAPIFolder
Dim itms As Outlook.Items
Dim itm As Object
Set olOutlook = New Outlook.Application
Set ns = olOutlook.GetNamespace("MAPI&quot
Set itms = ns.GetDefaultFolder(olFolderInbox).Items
Set Item = itms.Item
If Item.Unread = False Then
Item_Open = True
Else
Item_Open = True
End If

I always get an error message saying something about "user-defined type not defined".

Thanks a lot!

 

[jisoo22]

Just a quick note, what I'm actually trying to do is open ALL unread messages in the inbox. Is this possible?

Thanks,
Jisoo22

 

[jonscott8]

Not quite sure I understand what you are asking, but how about:

For Each itm In itms
If itm.Class = olMail Then
If itm.UnRead Then
itm.Display
End If
End If
Next Jon Hawkins

 

[NVI]

Hi
I want to built macro that moves messages from my inbox to personal folder

Thanks.

 

[pr15]

how to write a macro in outlook to automate the sending of a particular file to a particular person ?

 

[jisoo22]

NVI,

There's a function in Microsoft Outlook that will do that for you. It's called the "Rules Wizard" under the tools menu. You can have it move messages as it arrives by looking at the email address, subject, etc. and direct them to a folder you specify.

Jisoo22