Adjusting macro from L.Notes to Outlook

[Anomandaris]

Hi guys I have this macro that sends email from lotus notes, I need to change it so i can send the mail using outlook
What do i need to change here?


Sub FCMSend()
On Error Resume Next

Dim strbody As String, strsubject As String, first As String, _
second As String, third As String, sendto As String
Dim fourth As String
Dim tag1 As String
Dim intpr As Integer
Dim x As Integer
Dim y As Integer
Dim fifth As String
Dim saveit As String
Dim Recipient As String
Dim tagbody As String
Dim z As Integer
Dim refEnd As Integer
Dim RefST As String
Dim mail2 As String
Dim i As Integer
Dim buysell As String
Dim com As String

com = Sheet1.Range("x19")

intpr = Sheet6.Cells(3, 6).End(xlToRight).Column - 5
refEnd = Sheet1.Cells(18, 7).End(xlDown).row

For x = 8 To 16

If Sheet6.Cells(x, 4) <> 0 Then
tag1 = Sheet6.Cells(x, 4)

For z = 19 To refEnd
If Sheet1.Cells(z, 4) = tag1 Then
RefST = RefST & Sheet1.Cells(z, 7) & Chr(9) & Chr(9) & Sheet1.Cells(z, 5) & Chr(13)
Else
End If

Next


For y = 6 To intpr + 6
If Sheet6.Cells(x, y) <> 0 Then
tagbody = tagbody & Sheet6.Cells(3, y) & Chr(9) & Chr(9) & Sheet6.Cells(x, y) & Chr(10)
Else
End If
Next

second = "Hello" & Chr(13) & Chr(13) & "Confirming the following trades" & Chr(13) & Chr(13)
third = "Qty" & Chr(9) & "Average price" & Chr(9) & Chr(9) & Chr(9) & "Contract" & Chr(13)
fourth = Sheet1.Cells(x - 4, 36) & Chr(9) & Sheet6.Cells(x, 82) & Chr(9) & Chr(9) & Chr(9) & Sheet1.Range("B19") & Chr(9) & Chr(13) & Chr(10) & Chr(13)
fifth = Chr(13) & "Price" & Chr(9) & Chr(9) & "Qty" & Chr(10) & Chr(10)

If fourth = "" Then
fourth = "1" & Chr(9) & Sheet6.Range("g3") & Chr(9) & Chr(9) & Chr(9) & Sheet1.Range("B19") & Chr(9) & Chr(13) & Chr(10) & Chr(13)
Else
fourth = fourth
End If



If Sheet6.Range("f4") = 0 Then
tagbody = Sheet6.Range("g3") & Chr(9) & Chr(9) & Sheet6.Range("g4") & Chr(10)
Else
tagbody = tagbody
End If

strbody = second & third & fourth & "Ref" & Chr(9) & Chr(9) & "Qty" & Chr(13) & RefST & fifth & tagbody

Else
End
End If



If tag1 = "COM" Then
sendto = com
End If
If tag1 = "P" Then
sendto = "P random"
End If
If tag1 = "F" Then
sendto = "F random"
End If
If tag1 = "M" Then
sendto = "M random"
End If
If tag1 = "COW" Then
sendto = "Cow brain"
End If
If tag1 = "PEA" Then
sendto = "Peabrain"
End If
If tag1 = "X" Then
sendto = "X men"
End If
If tag1 = "B" Then
sendto = "barking"
End If
If tag1 = "RR" Then
sendto = "RoR"
End If


For i = 1 To 2
If i = 1 Then
mail2 = sendto
Else
mail2 = Sheet1.Range("Z10")
End If




MsgBox "Sending to " & mail2, vbInformation, "bogus info"
Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'THe current users notes mail database name
Dim MailDoc As Object 'The mail document itself
Dim AttachME As Object 'The attachment richtextfile object
Dim Session As Object 'The notes session
Dim EmbedObj As Object 'The embedded object (Attachment)
'Start a session to notes
Set Session = CreateObject("Notes.NotesSession")
'Next line only works with 5.x and above. Replace password with your password
' Session.Initialize ("password")
'Get the sessions username and then calculate the mail file name

UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
'Open the mail database in notes
Set Maildb = Session.GetDatabase("", MailDbName)
If Maildb.IsOpen = True Then
'Already open for mail
Else
Maildb.OPENMAIL
End If
'Set up the new mail document
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
MailDoc.sendto = mail2
MailDoc.Subject = " COM trades " & tag1 & Chr(32) & Sheet1.Range("B19")
MailDoc.body = strbody
MailDoc.SaveMessageOnSend = saveit
'Set up the embedded object and attachment and attach it
'If ATTACHMENT <> "" Then
' Set AttachME = MailDoc.CreateRichTextItem("Attachment")
' Set EmbedObj = AttachME.EmbedObject(1454, "", ATTACHMENT, "Attachment")
' MailDoc.CreateRichTextItem ("Attachment")
' End If
'Send the document
MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
MailDoc.send 0, Recipient
'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
Next
tag1 = ""
tagbody = ""
RefST = ""
tag1 = ""
Next



End Sub



i think i would have to change the last part of the code where its obviously opening up lotusnotes but im not sure how, any ideas? Thanks a lot in advance

[Kenneth Hobs]

Here is one example: http://vbaexpress.com/forum/showthread.php?t=21633

Here is a cdo method: http://www.vbaexpress.com/forum/showthread.php?t=22439

[Anomandaris]

Thanks Ken,

Its just that I'm not sure which bits to eliminate totally ---

MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"

do i keep the above bit?
I know how to email thru outlook, but not using Outlook MailDatabase, I've done it before by entering all email addresses in a separate sheet on the excel file. This case is different as the email addresses aren't in excel, but in Outlook.

[Kenneth Hobs]

That code is just for Lotus Notes.

Not sure that I know what Outlook database is.

You need contacts from Outlook? See if this helps. http://www.outlook-stuff.com/lang-en/tips-and-tricks/programming/289-serienmails-in-outlook-mit-anhaengen.html

[Anomandaris]

I cant access that site from work, thanks for trying to help me though. Really appreciate it.

anyone else have any ideas how to send emails to Outlook contact lists from Excel? Xld you around?

[Kenneth Hobs]

Not sure why you started another thread.

You seem to have 2 goals. (1) Use Outllook rather than Lotus Notes. (2) Use Outlook contacts rather than an Excel range of email addresses.

See this site for some good examples. http://www.rondebruin.nl/sendmail.htm

[Kenneth Hobs]

I attached the code from the site that you could not get to in Module2. It is rather long so I suggest that you try Ron's examples first.

[Anomandaris]

Thanks a lot Kenneth

That file has some good clues, I hope I can come up with something using these tips!