Send mails with attachments using a macro

Hey Guys,

I'm looking for a macro which will send some emails for me using outlook. But I just can't find what I'm searching for (I've been looking for hours now).
It's kinda complicated in my opinion, but you guys can probably figure this out. (atleast I hope so)

This is what I want to achieve:

I want the macro to fill in the receiver from column E
Than I need it to fill in the CC field with a adres specified in column G
Than I would like to add the subject from cell J5 (always the same cell so I can change the subject manually for all mails at once)
Than I want to attach the file, from which the location (localy stored) is specified in column B

Now the first part is done, I want to write my email message.
There are 2 different mails, one in english and one in dutch (my main language), which language the receiver speaks is specified in column H.

So if column H says "English" I want it to add the text from cell J1 to my mail, this cell says "Dear " (if Dutch I need it to add text from J2, saying "Geachte "
Now I need the macro to add the name from the receiver, which is specified in column C. (this should make the first sentense say "Dear John" for example

Now for the rest of the mail I need the macro to look up column H again, if that cell says "English" it needs to add the text from cell J3 which contains the rest of the message in HTML format. (if the cell in column H says "Dutch" it needs to add text from cell J4 which contains the same message in HTML format, but than in dutch)

I need the macro to send a file for every row filled with information in column A. (this is column that changes, I load a list with people I need to mail into that column, all other columns have VLOOPUP's to fill in the appropiate information (email adresses names etc.)

If someone could help me out on this it would mean the world to me.

Thanks in advance,
Jerry

I'd suggest starting with this page
Incidentally I believe he is a fellow dutchman
It is a very good source for mailing with outlook.

Ok, but do you have a basic structure already for the emailing?
I'm happy to help with the details of course.
If you can provide a copy of the workbook with some test data, I can probably be more useful?

You said CC is from column H, but you also say "If column H says Dutch then..."

Beste Warmerfare, here is something to start with:

Option Explicit
Sub Send_Files()

Dim OutApp As Object
Dim OutMail As Object
Dim ws As Worksheet
Dim i As Long
Dim lr As Long

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set ws = Sheets("Sheet1")
lr = ws.Range("A" & Rows.Count).End(xlUp).Row 'finds last row with data in column A, since you said send for every row with data in A
Set OutApp = CreateObject("Outlook.Application")

For i = 2 To lr 'assuming headers in row 1, loop starts at row 2

    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = ws.Range("E" & i).Value 'using E for "To" email adress
        .CC = ws.Range("G" & i).Value 'update to column with CC address
        .Subject = ws.Range("J5").Value
        If ws.Range("H" & i).Value = "English" Then
            .htmlbody = ws.Range("J1").Value & ws.Range("C" & i).Value & "<br><br>" & ws.Range("J3").Value
        ElseIf ws.Range("H" & i).Value = "Dutch" Then
            .htmlbody = ws.Range("J2").Value & ws.Range("C" & i).Value & "<br><br>" & ws.Range("J4").Value
        End If
        'if there is a file specified in B then add attachment
        If ws.Range("B" & i).Value <> vbNullString Then .Attachments.Add ws.Range("B" & i).Value
        .Send  'Or use .Display
    End With
    
    Set OutMail = Nothing
Next i

Set OutApp = Nothing
With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

End Sub

Driveing for a few hours??? That is enough to drive around all of Nederland a few times!! LOL

I updated my code post with G for the CC field. Drive safe, and hope it works out for you.

If you used plain text that should still have worked as part of the email message... are the Cells J3/J4 correct? Did it at least put Dear/Geachte, and the name?

When I tested it on my computer it worked fine... but I wonder.... my code is based on the worksheet being called "Sheet1" is that the name of your sheet with data? If the sheet has a different name then you must change

Set ws = Sheets("Sheet1")

to use the correct worksheet name.

Otherwise, can you provide a workbook with 1 or 2 lines of data? emails and names don't need to be real, just structure must be the same, and some kind of value in the fields for language name and message.

Warmefare je hebt idd gelijk.... Zolang de sheet Sheet1 heet moet het eigenlijk goed gaan. Maar ja... Als je het blad kan sturen dan zetten we dit wel in orde

Hey,

Ik heb gevonden wat er mis was, voor het nederlandse bericht werd de taal van communicatie uit vakje H gehaald, terwijl deze in vakje I aangegeven stond

Na het aanpassen word het bericht gewoon in de mail geplakt, alleen niet in de juiste opmaak

Hij slaat enters over en plakt de gehele tekst achter elkaar, maar daar ga ik nog heel even mee spelen Gefixt, heb de email-tekst in html in de macro gezet, en hem als een string op laten roepen. Een voor engels, en een voor nederlands.

Superbedankt voor al je hulp Arkari. Je bent een held !

Je had gezegd dat het bericht op het blad al in in HTML was.... maar in html zijn "enters" "<br>". Is dat misschien het probleem?

probeer anders maar

If ws.Range("I" & i).Value = "EN" Then
            .htmlbody = ws.Range("J1").Value & ws.Range("C" & i).Value & "<br><br>" & Replace(ws.Range("J3").Value, Chr(10), "<br>")
        ElseIf ws.Range("I" & i).Value = "NL" Then
            .htmlbody = ws.Range("J2").Value & ws.Range("C" & i).Value & "<br><br>" & Replace(ws.Range("J4").Value, Chr(10), "<br>")
        End If

het is ook mogelik dat het chr(13) is in plaats van 10.