Quick assistance with VBA code

[brunette]

Hi everyone,

I've been compiling code to export emails from Outlook 2007 - Excel 2007. I've gotten it to work 95% the way I want it, but there is one thing that's bothering me. My knowledge of VBA isn't yet as good as I'd like it to be so I can't understand why it's doing what it's doing. Perhaps you can explain?

The piece of code in question is this:

If InStr(1, msg.SenderEmailAddress, "501288010", vbTextCompare) > 0 Then
    varSender = "Name1 Name2"
Else
    varSender = msg.SenderEmailAddress
End If
If InStr(1, msg.SenderEmailAddress, "CN=RECIPIENTS/CN=", vbTextCompare) > 0 Then
    varSender = "SSO: " & Right(msg.SenderEmailAddress, 9)
Else
    varSender = msg.SenderEmailAddress
    varSender = msg.SenderName
End If

This is what it's returning:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">To</td><td style=";">From</td><td style=";">Subject</td><td style=";">Sent</td><td style=";">Received</td><td style=";">Folder</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">JOE SLOW</td><td style=";">SSO: N=CPGDTEC</td><td style=";">RE: 147 - UX19954</td><td style="text-align: right;;">30-08-2011 16:40</td><td style="text-align: right;;">30-08-2011 16:40</td><td style=";">August</td></tr></tbody></table><br>
As you can see, it's obviously using the rule that's in the VBA code to return "SSO: N=CPGDTEC" in the "From" column, but what I really need is to see the actual name(s) of whoever sent the mail.

This is where my VBA knowledge falls short.
1) How would I go about changing this?
2) Can I tweak the varSender = "Name1 Name2" rule to recognize instances where there may be more or less than 2 words in a sender's name?
3) Could that be the reason why it's not reacting correctly to the rule I'm having hassles with?


Thanks in advance,
Bru

 

[brunette]

In the event that this might be helpful to anyone else in the future, I've figured it out.

All I had to do was to remove the entire "if" section and replace it with SenderName.

Piece of code, therefore, looks as follows:

Set msg = itm
intRowCounter = intRowCounter + 1: Set rng = wks.Cells(intRowCounter, intColumnCounter): rng.Value = msg.To
varSender = msg.SenderName

Obviously, that's only a piece of it, but hopefully you can see what I did to fix it. It now shows as follows:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">To</td><td style=";">From</td><td style=";">Subject</td><td style=";">Sent</td><td style=";">Received</td><td style=";">Folder</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">JOE SLOW</td><td style=";">FRANK FAST</td><td style=";">RE: 147 - UX19954</td><td style="text-align: right;;">30-08-2011 16:40</td><td style="text-align: right;;">30-08-2011 16:40</td><td style=";">August</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Regards,
Bru

 

[Saagar]

Could you please reply with the whole code. I have never thought about exporting emails this way. I could use this macro.

Thanks.